Be kind enough to convert formula to vba macro

[Rockymtnhigh]

Would like to convert this array formula to vba so I can run as macro:

{=REPT("0",MAX(LEN($B$2:$B$349))-LEN(B2))&B2}

Also, any way to make it dynamic as there may be less than or greater than B349?

Thank you.

 

[Scott Huish]

What's it supposed to do?

 

[Scott Huish]

Never mind, I think I figured it out, it supposed to format the number in B2 to the length of the longest number.

This is a simpler formula to do that:

=TEXT(B2,REPT("0",LEN(MAX(B1:B7))))

Confirmed with CTRL-SHIFT-ENTER

 

[Scott Huish]

Probably faster as a formula but here you go, I didn't know where you wanted the result so I put it in C1:

Sub test()
Dim c As Range, t As Long
For Each c In Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)
    If Len(c) > t Then t = Len(c)
Next
Range("C1").NumberFormat = "@"
Range("C1") = Application.WorksheetFunction.Text(Range("B2"), String(t, "0"))
End Sub

Probably still work as I posted it, but my formula should've been:

=TEXT(B2,REPT("0",MAX(LEN(B1:B7))))

 

[Rockymtnhigh]

Formula inserts leading zeros as follows:

1st would become 001st
17th would become 017th
123rd stays the same 123rd

Also, any way to make it into a vba macro and make dynamic?

Thanks

 

[Scott Huish]

This will do it in place, if you want it in a different column, change the range in the second loop:

Sub test()
Dim c As Range, t As Long, r As Long
r = Range("B" & Rows.Count).End(xlUp).Row
For Each c In Range("B2:B" & r)
    If Len(c) > t Then t = Len(c)
Next

For Each c In Range("B2:B" & r)
    c = Right(String(t, "0") & c, t)
Next
End Sub

 

[Rockymtnhigh]

Hotpepper,

I'm not a "loop" guy as in I don't know anything about vba, could you please highlight exactly where that would be accomplished?

Do you ever get tired of answering questions from neophytes? ... Kind of like Tiger Woods playing a round of gold with a rookie!

 

[Scott Huish]

If you wanted the results in column C instead of the original column B you will have to offset where the value is going, so that would be 1 column over:

The second loop would become:

For Each c In Range("B1:B" & r)
    c.Offset(, 1) = Right(String(t, "0") & c, t)
Next

 

[Rockymtnhigh]

Thank you. It works perfectly.