How use RC formulas when fill three columns

[Abdo]

Hello
I have this code.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lr As Long
    If Target.Address = Range("G2").Address Or Target.Column = 5 Or Target.Column = 6 Then
        Application.EnableEvents = False
        lr = Cells(Rows.Count, "B").End(xlUp).Row

        Range("G2").FormulaR1C1 = "=RC[-2]-RC[-1]"
        Range("G3:G" & lr).FormulaR1C1 = "=R[-1]C+RC[-2]-RC[-1]"
        Range("G3:G" & lr).Value = Range("G3:G" & lr).Value
        Application.EnableEvents = True
    End If

End Sub

I try when fill column E or F then will fill two formula
in G2=E2-F2
G3=G2+E3-F3
G4=G3+E4-F4
and so on .
the code will show error in G2,G3 =#VALUE
any help guys,please?

 

[Abdo]

Dropbox

 

[MARK858]

It is because your 2nd formula is looking at the header row which is text due to your 2nd formula overwriting the first formula, as your last row is 2 at the time i.e. it is putting =G1+E2-F2 in cell G2 as the code is saying

Range("G3:G2").FormulaR1C1 = "=R[-1]C+RC[-2]-RC[-1]"

When there isn't an inv number in row 3 of column B

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lr As Long
    If Target.Address = Range("G2").Address Or Target.Column = 5 Or Target.Column = 6 Then
        Application.EnableEvents = False
        lr = Cells(Rows.Count, "B").End(xlUp).Row

        Range("G2").FormulaR1C1 = "=RC[-2]-RC[-1]"
      
        If lr < 3 Then lr = 3
      
        Range("G3:G" & lr).FormulaR1C1 = "=R[-1]C+RC[-2]-RC[-1]"
        Range("G3:G" & lr).Value = Range("G3:G" & lr).Value
        Application.EnableEvents = True
    End If

End Sub

 

[Abdo]

"=R[-1]C+RC[-2]-RC[-1]"

this is what I got when use recorded macro then I set for this formula in G3 , I don't understand what the header is relating !
should start from row2,3
so what I suppose to fix it?

 

[MARK858]

so what I suppose to fix it?

See the line in red in the code in my last post

 

[MARK858]

should start from row2,3

From my last post

is putting =G1+E2-F2 in cell G2 as the code is saying
VBA Code:
Range("G3:G2").FormulaR1C1 = "=R[-1]C+RC[-2]-RC[-1]"

 

[Abdo]

it works.

        Range("G2").FormulaR1C1 = "=RC[-2]-RC[-1]"<br>

I suppose when write in e2 or f2 so in g2=e2-d2 , but will show error
after that when write in e3 or f3 should look to g2 , not g1 . I don't select cells in the headers to start from headers!
so I don't used to state condition as you did it.in some similar cases no need use IF condition and works ,but in here I'm confused!

 

[MARK858]

It isn't

Range("G2").FormulaR1C1 = "=RC[-2]-RC[-1]"

that caused the issue, that was writing in the cell correctly.

It is the 2nd formula, when lr is 2 the cells referenced with

Range("G3:G" & lr)

are

 Range("G3:G" & 2)

or

Range("G3:G2")

which is exactly the same range as

Range("G2:G3")

and so it was overwriting the formula in G2 with

=G1+E2-F2

You could also probably do

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lr As Long
    If Target.Address = Range("G2").Address Or Target.Column = 5 Or Target.Column = 6 Then
        Application.EnableEvents = False
        lr = Cells(Rows.Count, "B").End(xlUp).Row

'        Range("G2").FormulaR1C1 = "=RC[-2]-RC[-1]"
'
'        If lr = 2 Then lr = 3
        
        Range("G2:G" & lr).FormulaR1C1 = "=SUM(R[-1]C,RC[-2])-RC[-1]"
        Range("G3:G" & lr).Value = Range("G3:G" & lr).Value
        Application.EnableEvents = True
    End If

End Sub

it works.

Happy it helped

 

[Abdo]

        Range("G3:G" &amp; lr).Value = Range("G3:G" &amp; lr).Value<br>

to avoid show formulas I suppose this

        Range("G2:G" & lr).Value = Range("G2:G" & lr).Value

I prefer the last answering .
thanks for your time and help.

 

[MARK858]

I prefer the last answering .
thanks for your time and help.

You're welcome (and yes to your last code if you want G2 included, left it as that was how you had it in post 1)