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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,382
Messages
5,836,931
Members
430,463
Latest member
mikmob

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