Format monetary amount to 9 digits with No decimals without rounding
 

I need to convert a standard dollar amount (eg 157.62) to a 9-digit
number without decimals or rounding (eg 000015762). The formulas I've
tried so far a

For Each rw In ActiveSheet.UsedRange.Rows
If Application.CountIf(rw, "*monetary*") 0 Then
With rw
.NumberFormat = "0000000.00;(0000000.00)"
End With
End If
Next

Which formats as "0000157.62". And:

For Each rw In ActiveSheet.UsedRange.Rows
If Application.CountIf(rw, "*monetary*") 0 Then
With rw
.NumberFormat = "000000000;(000000000)"
End With
End If
Next

Which formats as "000000158".

Does such a thing exist? Any assistance would be greatly appreciated.
Thanks in advance.

Format monetary amount to 9 digits with No decimals without roundi
 

=TEXT(A1*100,"000000000")


--
Regards,
Dave


" wrote:

I need to convert a standard dollar amount (eg 157.62) to a 9-digit
number without decimals or rounding (eg 000015762). The formulas I've
tried so far a

For Each rw In ActiveSheet.UsedRange.Rows
If Application.CountIf(rw, "*monetary*") 0 Then
With rw
.NumberFormat = "0000000.00;(0000000.00)"
End With
End If
Next

Which formats as "0000157.62". And:

For Each rw In ActiveSheet.UsedRange.Rows
If Application.CountIf(rw, "*monetary*") 0 Then
With rw
.NumberFormat = "000000000;(000000000)"
End With
End If
Next

Which formats as "000000158".

Does such a thing exist? Any assistance would be greatly appreciated.
Thanks in advance.

Format monetary amount to 9 digits with No decimals without roundi
 

Thanks for the quick response. When I tested your suggestion, I
received a "Compile Error: Sub or Function not defined". This is the
code as I have it:

For Each rw In ActiveSheet.UsedRange.Rows
If Application.CountIf(rw, "*monetary*") 0 Then
With rw
.NumberFormat = Text(A1 * 100, "000000000")
End With
End If
Next

I'm definitely a novice, so I'm not sure where to begin
troubleshooting. Thanks again for your help.

Format monetary amount to 9 digits with No decimals without ro
 

Heh, no that is an excel built in function, just place that formula in any
cell within the spreadsheet (not in a macro), and change the reference of A1
to the cell you wish to change. Sorry for not specifying :-)

--
Regards,
Dave


" wrote:

Thanks for the quick response. When I tested your suggestion, I
received a "Compile Error: Sub or Function not defined". This is the
code as I have it:

For Each rw In ActiveSheet.UsedRange.Rows
If Application.CountIf(rw, "*monetary*") 0 Then
With rw
.NumberFormat = Text(A1 * 100, "000000000")
End With
End If
Next

I'm definitely a novice, so I'm not sure where to begin
troubleshooting. Thanks again for your help.

Format monetary amount to 9 digits with No decimals without ro
 

I guess that shows how much of a novice I truly am. The problem is
that this macro is used on a spreadsheet which imports data from a CSV
file on a weekly basis. I also need to have this formula applied
conditionally to cells with the "monetary" data only. Because of this,
the only viable option I've found is to do it through a macro. Is
there a way to apply the formula you've given to variable row based on
the date of a specific cell?