Macro to fill down formual when new data entered

AshaG · Jan 15th 2019

I've been playing around with trying to get my sheet to fill down three formulas every time a new entry is added. And I want it to do it without having to manually run the macro

This is the code I've been playing with

Code

Private Sub Worksheet_Activate()


Dim LastRow As Long
Dim rngFormN As Range




Application.ScreenUpdating = False


With Worksheets("Upgrade Requests")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row


Set rngFormN = .Range("N3:N" & .Cells(.Rows.Count, "A").End(xlUp).Row)
rngFormN.Formula = "=IF(OR(E3="""", I3=""""), """", NETWORKDAYS(E3,I3))"
rngFormN.Offset(0, 1).Formula = "=IF(OR(E3="""", K3=""""), """", NETWORKDAYS(E3,K3))"
rngFormN.Offset(0, 2).Formula = "=IF(OR(K3="""", I3=""""), """", NETWORKDAYS(K3,I3))"


End With


Application.ScreenUpdating = True
End Sub

Display More

I've tried versions of it in selection change event too, but I can't seem to get it to work. It just makes my whole workbook freeze. Any ideas would be a huge help

Logit · Jan 15th 2019

The macro you have chosen ( Sub Worksheet_Activate() ) will only fire if you move to another sheet, then return to sheet Upgrade Requests. Hence the term "Activate".

Try placing your code in one of these macros :

Code

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)


End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


End Sub

Display More

AshaG · Jan 17th 2019

Thanks, Logit.

I tried SelectionChange before and it made my whole workbook freeze. And I just tried the Change and SelectionChange as you suggested and Excel had to restart.

Logit · Jan 17th 2019

Apologies for the lateness in responding. This Forum is not forwarding notices to me regarding new activity.

I placed your macro in the following sub and it works here.

Code

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LastRow As Long
Dim rngFormN As Range




Application.ScreenUpdating = False


With Worksheets("Sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row


Set rngFormN = .Range("N3:N" & .Cells(.Rows.Count, "A").End(xlUp).Row)
rngFormN.Formula = "=IF(OR(E3="""", I3=""""), """", NETWORKDAYS(E3,I3))"
rngFormN.Offset(0, 1).Formula = "=IF(OR(E3="""", K3=""""), """", NETWORKDAYS(E3,K3))"
rngFormN.Offset(0, 2).Formula = "=IF(OR(K3="""", I3=""""), """", NETWORKDAYS(K3,I3))"


End With


Application.ScreenUpdating = True
End Sub

Display More

For testing purposes I used Sheet1 as the sheet name. You will need to change the name to match your project.

Macro to fill down formual when new data entered

Participate now!

Share