excel worksheet getting new data every month how to update formulas on new incoming data rows

khana04

New Member
Joined
Dec 1, 2016
Messages
5
I have worksheet with column A,B , C, D, E , worksheet updates once a week through data connection to URL
Column A,B, C, D are populated during refresh but column E is calculated column for instance =(B/C)

what the best way to trickle down the formula to new rows every week automatically instead of opening the file and copy paste
the formula to new rows and in column E

thanks
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
For column E using the formula you posted, try this in a standard module.
Clears old results and returns new values to E column as far down as you have data in B column.

Howard


Code:
Option Explicit
Sub myLenColumn()
  Dim lRowCount&
  Columns("E:E").ClearContents
  
  lRowCount = Cells(Rows.Count, "B").End(xlUp).Row
  
  With Range("E1").Resize(lRowCount)
    .Formula = "=IFERROR(B1/C1,"""")": .Value = .Value
  End With
End Sub
 

khana04

New Member
Joined
Dec 1, 2016
Messages
5
Thanks a lot , This works great

For column E using the formula you posted, try this in a standard module.
Clears old results and returns new values to E column as far down as you have data in B column.

Howard


Code:
Option Explicit
Sub myLenColumn()
  Dim lRowCount&
  Columns("E:E").ClearContents
  
  lRowCount = Cells(Rows.Count, "B").End(xlUp).Row
  
  With Range("E1").Resize(lRowCount)
    .Formula = "=IFERROR(B1/C1,"""")": .Value = .Value
  End With
End Sub
 

khana04

New Member
Joined
Dec 1, 2016
Messages
5

ADVERTISEMENT

Thanks a lot , This works great


is there way to expand this to more columns for example I want to add formulas to column F and G for instance =(A1+B1) to Column F and =Sum(A1:E1) to column G, or do I have a new module for every calculated columns

thanks
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Try it this way.

Howard

Code:
Option Explicit

Sub myLenColumn_EFG()

  Dim lRowCount&
  Columns("E:G").ClearContents
  
  lRowCount = Cells(Rows.Count, "B").End(xlUp).Row
  
  With Range("E1").Resize(lRowCount)
    .Formula = "=IFERROR(B1/C1,"""")"
    .Offset(, 1).Formula = "=IFERROR(A1+B1,"""")"
    .Offset(, 2).Formula = "=IFERROR(SUM(A1:E1),"""")"
  End With
End Sub
 
Last edited:

khana04

New Member
Joined
Dec 1, 2016
Messages
5
is there way to stop it from deleting the first row header. other wise it works perfect

Thanks
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
This should do it. Where data and formulas start in row 2.

Howard


Code:
Sub myLenColumn_EFG_Row_2()
  Dim aRow As Long, eRow As Long
  
  aRow = Cells(Rows.Count, "B").End(xlUp).Row
  eRow = Cells(Rows.Count, "E").End(xlUp).Row
  
  Range("E2:G" & eRow).ClearContents
  
  With Range("E2").Resize(aRow - 1)

    .Formula = "=IFERROR(B2/C2,"""")"
    .Offset(, 1).Formula = "=IFERROR(A2+B2,"""")"
    .Offset(, 2).Formula = "=IFERROR(SUM(A2:E2),"""")"
    
  End With

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,133,536
Messages
5,659,376
Members
418,500
Latest member
Guru Prasad S

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