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

put 100 in a cell

copy it

select column A, then paste special
click divide under operation

then just format the column without the $ with 2 decimal.

--


Gary


"SITCFanTN" wrote in message
...
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

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


"SITCFanTN" wrote in message
...
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

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


"SITCFanTN" wrote in message
...
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

Okay, that answer raises some more questions and/or possible solutions. Note
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" wrote in message
...
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


"SITCFanTN" wrote in message
...
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

On Apr 4, 1:58*pm, "Rick Rothstein \(MVP - VB\)"
wrote:
Okay, that answer raises some more questions and/or possible solutions. Note
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" wrote in message

...



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

"SITCFanTN" wrote in message
...
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- Hide quoted text -

- 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.

On Thursday, April 3, 2008 8:42:00 PM UTC-5, SITCFanTN wrote:
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

Thanks a lot for your help..
Really appreciate it

wrote:
On Thursday, April 3, 2008 8:42:00 PM UTC-5, SITCFanTN wrote:
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

Thanks a lot for your help..
Really appreciate it


export as text, set number format to use , and . as separators, import
back to excel. It should recognize them as numbers. divide by 100.
change number format back to original one.

wrote :
On Thursday, April 3, 2008 8:42:00 PM UTC-5, SITCFanTN wrote:
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

Thanks a lot for your help..
Really appreciate it

=========================================
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

hi,

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, a écrit :
On Thursday, April 3, 2008 8:42:00 PM UTC-5, SITCFanTN wrote:
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

Thanks a lot for your help..
Really appreciate it

On Thursday, August 30, 2012 at 9:20:21 PM UTC-4, isabelle wrote:
hi,

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, a �crit :
On Thursday, April 3, 2008 8:42:00 PM UTC-5, SITCFanTN wrote:
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

Thanks a lot for your help..
Really appreciate it


Thanks so much Isabelle. This is the easiest way so far..that I learned today!