Divide entire column by 100
SITCFanTN
Values of
227.00 are shown as $22,700.00, 136.00 is shown as $13,600.00 aqnd 1331.00
is shown as 133,100.00. I need all the values in column I to be divided by
100 with the $ sign omitted. Can anybody help me with the VB code for this.
Thanks so much
Gary Keramidas
copy it
select column A, then paste special
click divide under operation
then just format the column without the $ with 2 decimal.
--
Gary
"SITCFanTN" <SITC...@discussions.microsoft.com> wrote in message
news:82087624-B5FF-44E9...@microsoft.com...
Rick Rothstein (MVP - VB)
you select the cell that says $22,700.00 (but is supposed to be 227), what
does it say in the Formula Bar? Also, right-click the cell, select the
Format Cells item from the popup menu that appears and click the Number tab
on the dialog box that appears. What item in the Category listing is
selected? If there are any listings or fields shown under the Sample field,
what values are in them?
Rick
"SITCFanTN" <SITC...@discussions.microsoft.com> wrote in message
news:82087624-B5FF-44E9...@microsoft.com...
SITCFanTN
Question 1 = 22700
Question 2= Currency
Data in Sample field = $22,700.00
I need the pennies to show so what I need the field to look like is 227.00.
I hope this helps, thanks so much.
Rick Rothstein (MVP - VB)
the emphasis in the following... IF you will NOT be using these values in
any further/future calculations, then you can make the value "look" like
what you want (but note that it will NOT actually be that value; hence the
caution about future use) by simply formatting the entry. Just use the same
procedure I asked you to follow in my previous response and select Custom
from the Category list and put 0\.00 in the Type field.
However, I'm willing to be you will want to use this value in other
calculations. If that is the case, my first question is why not use standard
entries? For 227, just type in 227 and, also, remove the format from the
cells in that column (change the Currency selection to General in the
Formatting Cells dialog). For values with penny amounts, just type in the
decimal point where it would normally be. Do you really think the saving
from typing this on additional character is worth the trouble it causes?
If you are insistent on not typing in the decimal point, then my next
question is... will you be creating all your entries in this column only one
time? If so, follow the procedure Gary laid out for you. If you will be
making multiple entries in this column over time, then you will need an
event procedure to handle adjusting the value. To do this, right click the
tab for the worksheet with this column on it and select View Code from the
menu that pops up. Copy/Past the following into the code window that
appears...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
On Error GoTo Whoops
If InStr(Target.Value, ".") = 0 Then
Application.EnableEvents = False
Target.Value = Target.Value / 100
Application.EnableEvents = True
On Error GoTo 0
End If
End If
Exit Sub
Whoops:
Application.EnableEvents = True
End Sub
Oh, and make sure to remove the format from the cells in that column (change
the Currency selection to General in the Formatting Cells dialog).
Rick
"SITCFanTN" <SITC...@discussions.microsoft.com> wrote in message
news:CAA1F90B-F361-4EE3...@microsoft.com...
Ivyleaf
>
> news:CAA1F90B-F361-4EE3...@microsoft.com...
>
>
>
> > Thanks Rick for your help...here are the answers to your questions
>
> > Question 1 = 22700
> > Question 2= Currency
> > Data in Sample field = $22,700.00
>
> > I need the pennies to show so what I need the field to look like is
> > 227.00.
>
> > I hope this helps, thanks so much.
>
> > "Rick Rothstein (MVP - VB)" wrote:
>
> >> Before you go dividing by 100, let's make sure we know what is going on.
> >> If
> >> you select the cell that says $22,700.00 (but is supposed to be 227),
> >> what
> >> does it say in the Formula Bar? Also, right-click the cell, select the
> >> Format Cells item from the popup menu that appears and click the Number
> >> tab
> >> on the dialog box that appears. What item in the Category listing is
> >> selected? If there are any listings or fields shown under the Sample
> >> field,
> >> what values are in them?
>
> >> Rick
>
> >>news:82087624-B5FF-44E9...@microsoft.com...
> >> >I have a spreadsheet where the currency value are formatted incorrectly.
> >> > Values of
> >> > 227.00 are shown as $22,700.00, 136.00 is shown as $13,600.00 aqnd
> >> > 1331.00
> >> > is shown as 133,100.00. I need all the values in column I to be
> >> > divided
> >> > by
> >> > 100 with the $ sign omitted. Can anybody help me with the VB code for
> >> > this.
>
> - Show quoted text -
Hi,
One thing I am not clear on is whether you want new data entered to be
divided by 100 (which appears to be Rick's interpretation from the
Worksheet_Change suggestion he offered, or if you have been given a
stack of data that is already in this format in which case Gary's
PasteSpecial method would be the simplest.
If you are keying this new data, one other suggestion would be to head
to 'Tools' -> 'Options' -> 'Edit' tab, and tick 'Fixed decimal places'
with a setting of -2. This will mean that every value you enter will
be automatically divided by 100 without having to run a Change macro.
If you already have the source data and it is not still being added
to, the following macro will do what you asked, but it does involve a
loop which Gary's method avoids:
Sub DivideCells()
Dim cell As Range
For Each cell In Range("I:I").SpecialCells(xlCellTypeConstants,
xlNumbers)
cell = cell.Value / 100
Next
Range("I:I").NumberFormat = "General"
End Sub
Cheers,
Ivan.
fab...@gmail.com
> I have a spreadsheet where the currency value are formatted incorrectly. Values of 227.00 are shown as $22,700.00, 136.00 is shown as $13,600.00 aqnd 1331.00 is shown as 133,100.00. I need all the values in column I to be divided by 100 with the $ sign omitted. Can anybody help me with the VB code for this. Thanks so much
Really appreciate it
witek
back to excel. It should recognize them as numbers. divide by 100.
change number format back to original one.
Bruno Campanini
Public Sub FormatNum()
Dim FirstCell As Range, SourceRange As Range, i
Set FirstCell = [Sheet2!H1]
Set SourceRange = FirstCell.Resize(FirstCell.End(xlDown).Row)
For Each i In SourceRange
i.Value = i / 100
Next
SourceRange.NumberFormat = "#,###.00"
End Sub
==========================================
Bruno
isabelle
write the number 100 in a cell, make a copy of this cell, select the range and do a paste special - division
--
isabelle
Le 2012-08-30 16:42, fab...@gmail.com a �crit :
