CODEWITHSUNDEEP
stringvbanumbers
F.P
F.P

Reputation: 17831

Create number prefix in VBA

I have several revision numbers I need to create folders from. So, lets say {4, 12, 534}. From this, I need to create folders that can be sorted accordingly;

\004\..
\012\..
\534\..

So, what I need to is to get the highest number and then see, how many filling zeros I need for every foldername so they are sorted correct after creating them.

How can I do this using VBA (Excel)? I tried converting them to strings and then dealing around with String operations, but this didn't quite work out.

I hope someone has a nice idea for this...

Upvotes: 0

Views: 5903

Answers (3)

RolandTumble
RolandTumble

Reputation: 4703

The simplest way is with Format (or Format$):

dim s As String

    s = Format$(revNum, "000")

Using "000" as the "format string" tells the function to pad with leading zeros, whereas "###" tells it not to pad. The difference between the function versions is the return type: Format returns a Variant (String), while Format$ returns an actual String.

Upvotes: 7

Alex K.
Alex K.

Reputation: 175766

Easiest to precompute the max then loop again and pad;

Dim nums() As String: nums = Split("4,12,534,9999", ",")
Dim i As Long
Dim max As Long

For i = 0 To UBound(nums)
    If (Len(nums(i)) > max) Then max = Len(nums(i))
Next

For i = 0 To UBound(nums)
    nums(i) = String(max - Len(nums(i)), "0") & nums(i)
    Debug.Print nums(i)
Next

Upvotes: 1

Lance Roberts
Lance Roberts

Reputation: 22842

I'm not sure why a simple string operation won't work out.

Select Case Len(folderstring)
  Case 1
    folderstring = "00" & folderstring
  Case 2
    folderstring = "0" & folderstring
  Case Else
    ' Do nothing here
End Case

OR

Dim zeroarray(1 To 3) As Variant

zeroarray = Array("00","0","")
folderstring = zeroarray(Len(folderstring)) & folderstring

Upvotes: 0

Related Questions