# Be kind enough to convert formula to vba macro

#### Rockymtnhigh

##### Board Regular
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.
What's it supposed to do?

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

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:
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

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:
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!

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:
Thank you. It works perfectly.

Replies
7
Views
271
Replies
5
Views
252
Replies
8
Views
177
Replies
0
Views
201
Replies
2
Views
226

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.

### Which adblocker are you using?

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

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