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

[khana04]

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

 

[L. Howard]

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

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]

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

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

 

[L. Howard]

Glad to hear, thanks for the feedback.

Howard

 

[khana04]

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]

Try it this way.

Howard

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

 

[khana04]

is there way to stop it from deleting the first row header. other wise it works perfect

Thanks

 

[L. Howard]

This should do it. Where data and formulas start in row 2.

Howard

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