VBA to convert format of a number

[hashim217]

Hi all,

I am new to this forum and have just started reading VBA for dummies. I am trying to convert a number to a specific format. I need the number to be 8 digits where the last three represent decimal places. For example, if the number is 35.75, then I would need it to be converted to 00035750. If the number is 1250, I would need it to be converted to 01250000. Please advise if there is a way I can do this. Thanks!

-Hash

 

[AlphaFrog]

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">35.75</td><td style="text-align: right;;">00035750</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1250</td><td style="text-align: right;;">01250000</td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=A1*1000</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=A2*1000</td></tr></tbody></table></td></tr></table><br />
Format column B with a Custom Number Format of 00000000


Or this method but the results in column B are text.
<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">35.75</td><td style="text-align: right;;">00035750</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1250</td><td style="text-align: right;;">01250000</td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=TEXT(<font color="Blue">A1*1000,"00000000"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=TEXT(<font color="Blue">A2*1000,"00000000"</font>)</td></tr></tbody></table></td></tr></table><br />


VBA

Range("A1").Value = Range("A1").Value * 1000
Range("A1").NumberFormat = "00000000"

 

[hashim217]

Thanks for the reply. I have 2 follow up questions.

1) Is there anyway that I can convert the contents of a cell to the new format. For example, if cell A1 has 8.25, I want cell A1 to change to 00008250 instead of using another cell.

2) Once I have the proper format, is there anyway for me to copy to visible content in the cell and not the value? For example, when I try to copy the cell that has 00008250 and paste it into another cell, it pastes 8.25 which is the value. I am trying to have it paste 00008250.

Any help would be greatly appreciated.

-Hash

 

[shg]

Put the value 1000 in a cell, and format the cell as 00000000. Then copy that cell, select the other cells, and do Paste Special, Multiply.

 

[AlphaFrog]

Thanks for the reply. I have 2 follow up questions.

1) Is there anyway that I can convert the contents of a cell to the new format. For example, if cell A1 has 8.25, I want cell A1 to change to 00008250 instead of using another cell.

2) Once I have the proper format, is there anyway for me to copy to visible content in the cell and not the value? For example, when I try to copy the cell that has 00008250 and paste it into another cell, it pastes 8.25 which is the value. I am trying to have it paste 00008250.

Any help would be greatly appreciated.

-Hash

Sub Test()
    Range("A1").NumberFormat = "@"                              [COLOR="Green"]'Format the cell as text[/COLOR]
    Range("A1").Value = Format(Range("A1") * 1000, "00000000")  [COLOR="Green"]'Convert[/COLOR]
    Range("A1").Copy Destination:=Range("A3")                  [COLOR="Green"] 'Copy and paste to cell A3 (includes text formatting)[/COLOR]
End Sub

 

[hashim217]

Sub Test()
Range("A1").NumberFormat = "@"
Range("A1").Value = Format(Range("A1") * 1000, "00000000")
Range("A1").Copy Destination:=Range("A3")
End Sub

Hi, how would I apply the code given to a range of cells. For example, I want the code to change the format for cell A2 and copy it down to the last cell that contains data in column A. Thank you all for your help. Also, how do I put the code into the box the way you have posted it?

-Hash

 

[AlphaFrog]

how do I put the code into the box the way you have posted it?

Look at my signature block below.

Sub Test()
    Dim Lastrow As Long, cell As Range
    
    Application.ScreenUpdating = False
    Lastrow = Range("A" & Rows.Count).End(xlUp).Row       'Last used row in column A
    Range("A1:A" & Lastrow).NumberFormat = "@"            'format range as text
    For Each cell In Range("A1:A" & Lastrow)
        cell.Value = Format(cell * 1000, "00000000")      'Convert each cell
    Next cell
    Range("A1:A" & Lastrow).Copy Destination:=Range("C1") 'copy to column C
    Application.ScreenUpdating = True
    
End Sub

 

[hashim217]

Alphafrog you are awesome! Thanks!