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.
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:

Rockymtnhigh

Board Regular
Joined
Feb 2, 2009
Messages
83

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:

Rockymtnhigh

Board Regular
Joined
Feb 2, 2009
Messages
83

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,924
Members
414,416
Latest member
Nobu

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
Top