New problem with formula

[Richard]

The formula stops counting after the first couple of
entries due to more debits in E3 than credits in F3. Is
there a way to keep the formula running, something like
the paste function.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
Range("G3").Formula = "=(IF(OR(E3,F3>0),G2-E3+F3,""""))"
Range("G3", Range("F65536").End(xlUp).Offset(3,
7)).FillDown
Range("G3").Formula = "=(IF(OR(E3,F3>0),G2-E3+F3,""""))"
Range("G3", Range("F65536").End(xlUp).Offset(3,
7)).FillDown
Application.EnableEvents = True

End Sub

 

[hgrove]

Richard wrote...

The formula stops counting after the first couple of entries due
to more debits in E3 than credits in F3. Is there a way to keep
the formula running, something like the paste function.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
Range("G3").Formula = "=(IF(OR(E3,F3>0),G2-E3+F3,""""))"
Range("G3", Range("F65536").End(xlUp).Offset(3, 7)).FillDown
Range("G3").Formula = "=(IF(OR(E3,F3>0),G2-E3+F3,""""))"
Range("G3", Range("F65536").End(xlUp).Offset(3, 7)).FillDown
Application.EnableEvents = True
End Sub

Do you mean to have duplicate .Formula and .FillDown calls?

If you want the destination range for the Fill to key off col E o
whichever of cols E and F have more entries, use a variable.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim n As Long

Application.EnableEvents = False

n = Application.WorksheetFunction.Min( _
Range("E65536").End(xlUp).Row, _
Range("F65536").End(xlUp).Row)

Range("G3").Formula = "=(IF(OR(E3,F3>0),G2-E3+F3,""""))"

Range("G3", Range("F" & n).Offset(3, 7)).FillDown

Application.EnableEvents = True

End Su