excel and leading zeros 1

[RicksAtWork]

I have the following:

0
1.111
5.111
0
1.123

How using vba only, do I convert the above to:

0.000
1.111
5.111
0.000
1.123


I just cant do it!!

Its very very frustrating!

 

[AnotherHiggins]

[A:A].NumberFormat = "0.000"

If the data is currently entered as text instead of numbers, then

[A:A].Value = [a:a].Value
[A:A].NumberFormat = "0.000"

Replace "A" with whatever column you need.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[RicksAtWork]

I have this function that I have written (with your help!!):

Private Sub To3DP()

Range("J6", "J88").Select

Range("J6", "J88").NumberFormat = "0.000"

Dim c As Range

For Each c In Selection

If IsNumeric(c.Value) Then

If (c.Value = 0) Then


c.Value = "0.000"

Else

c.Value = Round(c.Value, 3)

End If

End If

Next

End Sub


How do I change the function so I can pass in the range???

 

[AnotherHiggins]

Not sure why you're making it so difficult. I don't see any reason to loop through each cell in the selection. If you start with
0
and apply the formatting "0.000", you wind up with
0.000

If you don't, then your zeros are not formatted as numbers, they are formatted as text. That's what the second (two line) suggestion was for.

Try this:

sub ShortTest
Range("J6", "J88").Select
Selection.value = Selection.value
Selection.NumberFormat = "0.000"
end sub

The "Selection.Value = Selection.Value" changes any text-formatted numbers to actual numbers.

Copy the above code and paste it into a module, then press F8 so you can step through the macro one line at a time to see what it does. All of your zeros should be properly formatted.

If not, let me (us) know.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[RicksAtWork]

Aye - I was just being incredibly stupid!!!
cheers for your help!

 

[AnotherHiggins]

Glad you got it sorted.


[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.