Adjust formula rows automatically

[Danny]

Hi All

I have a spreadsheet that acts as a template. I copy a range of data into cells and to the left of that data there are multiple formulas.

Because the data (row numbers) ranges from week to week i would like the formulas to adjust automatically. Is there some code that can aid with that?

 

[BBD]

Hi Danny,

I suggest you to use a table format. The Formula will get filled down everytime you add data till the last row in the table.

Here is a sample file for using a table.

Cheers,
BD

 

[Danny]

Hi BBD

Thank you for the suggestion, I am currently in the process of making a large Macro/ VBA script that will generate a report automatically. Was hoping for a script that could be added to the one I'm creating.

Thank you very much for the suggestion though.

Danny

 

[BBD]

Hi Danny,

Two methods I am suggesting.

1. If the formula already present in the first line - Code to copy the same till the last data available row.

Sub FillDown_1()
Dim row As Long
row = ActiveSheet.Cells.Find(What:="*", After:=Cells(Cells.Rows.Count, Cells.Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
Range("b1").Copy Range("b1:b" & row) 'Change the range as appropriate
End Sub

2. If you want to enter the formula through VBA.

Sub FillDown_2()
Dim row As Long
row = ActiveSheet.Cells.Find(What:="*", After:=Cells(Cells.Rows.Count, Cells.Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
Range("b1:b" & row).FormulaR1C1 = "=RC[-1]*5" 'Change the range and formula as appropriate
End Sub

Cheers,
BD

 

[Danny]

BBD,

Thank you, thats pretty much perfect. Thank you.

As the data can increase and decrease, is there a way to clear the formula cells if less data is copied in?

 

[BBD]

Danny,

Use the below.

Public function row() as long
row = ActiveSheet.Cells.Find(What:="*", After:=Cells(Cells.Rows.Count, Cells.Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
End function
  
Sub FillDown_2()
Range("b1:b"&row).ClearContents    'Change the range as appropriate
Range("b1:b" & row).FormulaR1C1 = "=RC[-1]*5" 'Change the range and formula as appropriate
End Sub

Cheers,
BD

 

[Danny]

BBD,

Thank you very much for your help.

Have really aided me, much appreciated.

Danny

 

[BBD]

Glad to help !!