Macro Help when working with decimals 2

[glthornton]

Hi everyone, I need some help in converting an cell that contains a salary value.

The incoming values could look like this:  83.4 or 99.25 or 24

The problem is that I need to convert this format into a zero filled decimal implied value.

And I need it to look like this:  0008340 or 0009925 or 0002400

I have tried many different combinations to do this, but I'm running out of suggestions in order to remove any decimal points and to add the proper number of zero's to the front as well as the back to ensure proper decimal alignment.

I would greatly appreciate any assistance that you could provide. Kind regards. Glenn

 

[longhair]

glthorton,
don't have time to write the code at the moment, but what i have done in the past is...
find the location of the decimal point the number.
take everything before it and put it in 1 var, everything after it into another var.
pad the 1st var to be the correct length, back fill the second var to be the correct length.
put var1 and var 2 bqack together.
write to your cell.
regards,
longhair

 

[AnotherHiggins]

Just to make sure you're aware - you'll wind up with text values, not true numbers.

Because you are dealing with dollar amounts it is easy to get rid of the decimals - just multiply by 100.

Assuming that your values are in column A:

Sub Convert()
    Range("A:A").NumberFormat = "@"
    For i = 1 To Range("A" & Application.Rows.Count).End(xlUp).Row
        Range("A" & i) = _
            Application.WorksheetFunction.Rept("0", 7 - Len(Range("A" & i) * 100)) & Range("A" & i) * 100
    Next i
End Sub

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Markus4]

if the incoming value is dimmed at least as long:

Format(incomingValue * 100, "0000000")

returns the desired result as a string

 

[xwb]

I thought the worksheet function was text, not format. Do you really need VBA to do this? Say column A contains the figures and you wish to put your format in column B. Just set B1 to

=text(A1*100,"0000000")

then copy it for as many columns as you wish.

 

[Markus4]

xwb: glthornton posted in VBA forum. That's why I assumed (s)he wanted code, not a worksheet function.

 

[glthornton]

Thank you all (including John) for your help and suggestions!! You've provided me with all the right information that I need. Markus, xwb was right in that I wanted to see an example in a worksheet function even though I wasn't clear in the beginning. You provided me with the proper syntax and xwb provided me the example. I don't want to speak for all programmers, but for me personally, I prefer the actual example that way I can logically apply it to my application. Anyway, thank you again everyone. Glenn