VBA Formatting Currency as a Fixed Digit Number With Leading and Trailing Zeros

speedkreature

New Member
Joined
Jan 7, 2014
Messages
2
I'm not to sure how else to title this...

Any help is appreciated. I spend most of my time coding in C and Bash, but this got thrown my way and it's been a struggle the whole way. Some of the code for this project is copied from examples elsewhere, some of it is original.

I'm importing data from CSV's exported by our ERP. The data requires a number of modifications before it can be submitted to the next stage of processing in a third party system. One of the columns contains currency (US dollar) values.

In the CSV, the values are represented as:
155.00
134.97
1389.18
etc.

Once they've been imported from the CSV, Excel strips trailing zeros unless the column is formatted as a currency. What I actually need, using the example values above is:
0000015500
0000013497
0000138918

I thought about multiplying the values by 100 then formatting them with "0000000000" but I can't get the multiplication to work out--syntax errors I'm sure. Below is the relevant area of code where I'd want to put this function.

Code:
lastRow = Range("A" & Rows.Count).End(xlUp).Row

For Each thing In Sheets(1).Range("E1:E" & lastRow)
    If thing <> "" Then
        Cells(thing.Row, "D") = Cells(thing.Row, "E")
        Cells(thing.Row, "E") = ""

        ' TODO: Take data from D:thing.Row, multiply by 100, and replace.  We'll format latter.
        
    End If
Next

At this stage I'm looking for any way I can get the formatting I need to work with my data.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
You may be having problems multiplying because of storing numbers as text. Try a conversion first:

Code:
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] Range
lastRow = Range("A" & Rows.Count).End(xlUp).Row

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] c [COLOR="Navy"]In[/COLOR] Sheets(1).Range("E1:E" & lastRow)
    [COLOR="Navy"]If[/COLOR] (c.Value & "") <> "" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] IsNumeric(c.Value) [COLOR="Navy"]Then[/COLOR]
            c.Offset(0, -1) = CDbl(c.Value) * 100
        [COLOR="Navy"]Else[/COLOR]
            c.Offset(0, -1) = c.Value
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]Next[/COLOR] c


You can do the formatting at the same time too, if you want. Otherwise, you still see how to use the format() function in this example. It should return a text value, but that would be what you probably want to pass on, since you need the leading zeroes.
Code:
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] Range
lastRow = Range("A" & Rows.Count).End(xlUp).Row

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] c [COLOR="Navy"]In[/COLOR] Sheets(1).Range("E1:E" & lastRow)
    [COLOR="Navy"]If[/COLOR] (c.Value & "") <> "" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] IsNumeric(c.Value) [COLOR="Navy"]Then[/COLOR]
            c.Offset(0, -1) = Format(CDbl(c.Value) * 100, "0000000000")
        [COLOR="Navy"]Else[/COLOR]
            c.Offset(0, -1) = c.Value
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]Next[/COLOR] c
 
Upvote 0
You may be having problems multiplying because of storing numbers as text. Try a conversion first:

Code:
[COLOR=Navy]Dim[/COLOR] c [COLOR=Navy]As[/COLOR] Range
lastRow = Range("A" & Rows.Count).End(xlUp).Row

[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] c [COLOR=Navy]In[/COLOR] Sheets(1).Range("E1:E" & lastRow)
    [COLOR=Navy]If[/COLOR] (c.Value & "") <> "" [COLOR=Navy]Then[/COLOR]
        [COLOR=Navy]If[/COLOR] IsNumeric(c.Value) [COLOR=Navy]Then[/COLOR]
            c.Offset(0, -1) = CDbl(c.Value) * 100
        [COLOR=Navy]Else[/COLOR]
            c.Offset(0, -1) = c.Value
        [COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
    [COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
[COLOR=Navy]Next[/COLOR] c


You can do the formatting at the same time too, if you want. Otherwise, you still see how to use the format() function in this example. It should return a text value, but that would be what you probably want to pass on, since you need the leading zeroes.
Code:
[COLOR=Navy]Dim[/COLOR] c [COLOR=Navy]As[/COLOR] Range
lastRow = Range("A" & Rows.Count).End(xlUp).Row

[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] c [COLOR=Navy]In[/COLOR] Sheets(1).Range("E1:E" & lastRow)
    [COLOR=Navy]If[/COLOR] (c.Value & "") <> "" [COLOR=Navy]Then[/COLOR]
        [COLOR=Navy]If[/COLOR] IsNumeric(c.Value) [COLOR=Navy]Then[/COLOR]
            c.Offset(0, -1) = Format(CDbl(c.Value) * 100, "0000000000")
        [COLOR=Navy]Else[/COLOR]
            c.Offset(0, -1) = c.Value
        [COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
    [COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
[COLOR=Navy]Next[/COLOR] c


Your second example more or less got what I wanted, though I still had to apply formatting via Columns("<column>:<column>").NumberFormat = "0000000000". All the same, this problem has been resolved. Thank you!
 
Upvote 0
Okay. Possibly the cell needs to be given text formatting first, then the value changed.

Code:
Dim c As Range
lastRow = Range("A" & Rows.Count).End(xlUp).Row

Sheets(1).Range("E1:E" & LastRow).NumberFormat = "@" '//apply text format to all cells
For Each c In Sheets(1).Range("E1:E" & lastRow)
    If (c.Value & "") <> "" Then
        If IsNumeric(c.Value) Then
            c.Offset(0, -1) = Format(CDbl(c.Value) * 100, "0000000000")
        Else
            c.Offset(0, -1) = c.Value
        End If
    End If
Next c

I would be careful with changing the cell formatting to "0000000000". With Excel this can mean that the "format" is to show leading zeroes, but that is not necessarily anything other than the displayed appearance. You probably want the underlying value to have the leading zeros, if you are passing this off to another program. For that, the cells must have "text" format first, or Excel automatically converts such values to numbers.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top