Hi! I'm working on a macro, where some of the action has to do with adding zeroes in front of numbers, so that every number gets the same amount of digits. 4 to be exact. This means that the number 1 is transformed to 0001. 12 -> 0012, and of course 123 will be 0123. You get the point. Every number gets 4 digits, and 0 is the filler.
I am a total newbie when it comes to these things, so my script consists of shameless cutting and pasting from other peoples works. This also means that I don't have the slightest clue of what to do when things aren't going my way.
Everything is going perfectly, except for one important thing: The macro treats numbers with decimals in a way I absolutely don't want it to. I want for example 12,3 to become 0012,3 -- but the macro insists on not changing it at all. I figured this happens because the comma (and the numbers behind it) are counted too. Therefore, a number like 12,3 already has four digits. But I want it to be 0012,3! This is making my head hurt.
My question is: Are there any way of making the program ignore the comma and all the other digits behind it? So that they are not counted. Or is there another way of dealing with this problem? Most of my numbers are without decimals, but the ones who do have them are causing severe damage to my nerves.
All help is tremendously appreciated!
(I don't know if I have explained myself good enough, but I hope I have. I'm from Norway, and my English isn't top notch.)
If you want to take a look at the code, here it is:
I am a total newbie when it comes to these things, so my script consists of shameless cutting and pasting from other peoples works. This also means that I don't have the slightest clue of what to do when things aren't going my way.
Everything is going perfectly, except for one important thing: The macro treats numbers with decimals in a way I absolutely don't want it to. I want for example 12,3 to become 0012,3 -- but the macro insists on not changing it at all. I figured this happens because the comma (and the numbers behind it) are counted too. Therefore, a number like 12,3 already has four digits. But I want it to be 0012,3! This is making my head hurt.
My question is: Are there any way of making the program ignore the comma and all the other digits behind it? So that they are not counted. Or is there another way of dealing with this problem? Most of my numbers are without decimals, but the ones who do have them are causing severe damage to my nerves.
All help is tremendously appreciated!
(I don't know if I have explained myself good enough, but I hope I have. I'm from Norway, and my English isn't top notch.)
If you want to take a look at the code, here it is:
Code:
Sub AddZeros()
Dim Cl
Selection.NumberFormat = "@"
For Each Cl In Selection
'Change the 10 below to the total desired Length of the cell
'0's will be added to reach this length.
Cl.Value = PadVal(Cl.Value, 4, "0", False)
Next Cl
End Sub
Function PadVal(ByVal StartVal As String, EndLen As Integer, PadWith As String, Optional AtEnd As Boolean = False) As String
'Arguments
'StartVal = Value to be "Padded"
'EndLen = The desired total charecter count of value
'PadWith = The charecter to add to the StartVal to reach the desired EndLen
'AtEnd = Pad at end of the StartVal string... True pads at end, False pads at beginning.
Do While Len(StartVal) < EndLen
Select Case AtEnd
Case False
StartVal = PadWith & StartVal
Case True
StartVal = StartVal & PadWith
End Select
Loop
PadVal = StartVal
End Function
Last edited: