Filling a formula down the column

streasure

New Member
Joined
Oct 17, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hello,
I am looking to fill a formula down a column using a macro. I tried to just to the record a macro but my problem is that I get a different size list of numbers each time, so that will not work.
The formula is a nested replace =REPLACE(REPLACE(A2,4,0,"."),8,0,".") -- where the original number sits in A2 - It adds two periods to form a phone number.

I am having an issue finding out how to fill down to the last number present in column A. I had it filling till like 10k but that is ridiculous if there is only 50 or so lines.

Please help!
Thank you.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

streasure

New Member
Joined
Oct 17, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Also -- sorry -- I forgot to add to the post, that I need it to be copy/pasted so that it is number format.

Don't know how to edit op.
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,799
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
How are you determining how far to fill down?

Do you have some sample data of the sheet that we can look at?
 

streasure

New Member
Joined
Oct 17, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I dont know -- without the macro i would just double click to fill down the column and it would fill until there were no more numbers in the first column.

This doesnt work for the macro though because it will only fill per how long the original data set is. What i did after that though was fill till the longest data i had and that was 10k numbers in the first column (like a list of 10k phone numbers) but that is annoying when there are only 100 numbers in the first column on other sheets. Because then it would juat fill to 10k no matter what and would make the sheet laggy.

All data sheets have a 10 digit number in the first column (phone number like: xxxxxxxxxx) that i need to add periods to (xxx.xxx.xxxx). The only difference is how many. There could be 100 in column A or 10k in column A -- thats where im having the problem. How do i fill the formula in column B to the bottom even if it is 50 to 10k?? I need it to stop when column A stops.

Does that make sense?
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,799
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

Ok, so now we know that you want to run this on several sheets.

Are you wanting to run it on all of the sheets in the workbook?
 

streasure

New Member
Joined
Oct 17, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I am looking for a macro that can be run on an sheet on any workbook. The starting column will always be column A and i would like it to be filled to column B.
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,799
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Didn't really answer my last question.

But here you go, a macro that can be adjusted to run on a sheet that you tell it to run on:

VBA Code:
Sub FilldownMacro()
'
    Dim LastRowInSheet  As Long
    Dim WS              As Worksheet
'
    Set WS = Sheets("Sheet1")                                                       ' <--- Set this to the sheet name
'
    LastRowInSheet = WS.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row   ' Returns a Row Number
'
    WS.Range("B2").Formula = "=REPLACE(REPLACE(A2,4,0,"".""),8,0,""."")"
    WS.Range("B2").AutoFill Destination:=WS.Range("B2:B" & LastRowInSheet), Type:=xlFillDefault
End Sub
 

streasure

New Member
Joined
Oct 17, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Oh ok I see what you are asking. I would like it to be a push button macro, where I would select the first line of the column, and run the macro to the line to the right. So it could be run any time in any workbook.

I guess. Sorry If I am not bein clear.
 

Forum statistics

Threads
1,147,452
Messages
5,741,209
Members
423,649
Latest member
steel1968

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