Be kind enough to convert formula to vba macro

Rockymtnhigh

Board Regular
Joined
Feb 2, 2009
Messages
83
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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
 
Upvote 0
Probably faster as a formula but here you go, I didn't know where you wanted the result so I put it in C1:

Code:
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))))
 
Last edited:
Upvote 0
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
 
Upvote 0
This will do it in place, if you want it in a different column, change the range in the second loop:

Code:
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
 
Last edited:
Upvote 0
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!
 
Upvote 0
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:

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

Forum statistics

Threads
1,211,963
Messages
6,105,107
Members
447,947
Latest member
OX_2005

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top