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.

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

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.

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.

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?