Make the worksheet dynamic

[kvemperor]

In this worksheet i have the function in cell G9 =B9+G8-E9 so every time i am putting a value in the column B or in the column E then when i drag down the cell (of the same row)in the column G then i have my result.Is it possible this to make it dynamic?(with vba or any other way? i mean instead of drugging down the cell of the column G down when i am putting a value in the column B or in the column E the result comes automatically in the column G.
I wouldn't mind when i was putting the date in the column F then automatically comes the result in the column G
thank you in advance

 

[abdelfattah]

Nο .now i put the values in the columns D and G instead of the columns C and F .I am also putting something to the column E but its an other thing

so why you didn't say that from the beginning as I ask you many times?

just post the whole requirements without any adjusting every time until to find help from the rest of others members.

and which you columns entering ?

just I want if you want enter values in columns C ,F as in earlier

anyway try this and see how goes .

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Or Target.Row < 8 Then Exit Sub
Application.EnableEvents = False
If Target.Column = 5 And Target.Row > 8 Or Target.Column = 7 And Target.Row > 8 Then
Dim LR1 As Integer
LR1 = Range("B" & Rows.Count).End(xlUp).Row
For s = 9 To LR1
Range("I9:I" & s).FormulaR1C1 = "=RC[-6]+R[-1]C-RC[-3]"
Next
End If
Application.EnableEvents = True
End Sub

 

[abdelfattah]

Hallo my friend.Still no working

sorry buddy!
seems wrong in this line

Range("I9:I" & s).FormulaR1C1 = "=RC[-6]+R[-1]C-RC[-3]"

just change this

Range("I9:I" & s).FormulaR1C1 = "=RC[-6]+R[-1]C-RC[-3]"

to this

Range("I9:I" & s).FormulaR1C1 ="=RC[-4]+R[-1]C-RC[-2]"

then will become

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Or Target.Row < 9 Then Exit Sub
Application.EnableEvents = False
If Target.Column = 5 And Target.Row > 8 Or Target.Column = 7 And Target.Row > 8 Then
Dim LR1 As Long
LR1 = Range("B" & Rows.Count).End(xlUp).Row
For s = 9 To LR1
Range("I9:I" & s).FormulaR1C1 = "=RC[-4]+R[-1]C-RC[-2]"
Next
End If
Application.EnableEvents = True
End Sub

if the calculation is wrong , just tell me how the formula should be .

 

[Alex Blakenburg]

If you convert your data area to an Excel Table then your formulas will automatically fill down to any additional rows.

How to Create Excel Tables and Fix Excel Table Problems
Everything You Need to Know About Excel Tables | How To Excel

 

[kvemperor]

If you convert your data area to an Excel Table then your formulas will automatically fill down to any additional rows.

How to Create Excel Tables and Fix Excel Table Problems
Everything You Need to Know About Excel Tables | How To Excel

thank you very much for your answer.unfortunately it doesn t make the result of the calculation automatically,if i make the range into table

 

[Alex Blakenburg]

So you have converted the area into a table and can see the table design tab on the toolbar, and copied the formula to all rows initially and it still doesn't do what you want ?

 

[kvemperor]

So you have converted the area into a table and can see the table design tab on the toolbar, and copied the formula to all rows initially and it still doesn't do what you want ?

yes my friend.it writes value instead of the result

 

[abdelfattah]

@kvemperor
I'm not sure if I understand your OP
but I supposes in G8 will contain value and from G9 will add formula . if you want just showing value not formula in column G ,then enable the line disabled as in comment inside the code
so this my try .
copy the code and paste into sheet module , not standard module

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Or Target.Row < 1 Then Exit Sub
Application.EnableEvents = False
If Target.Column = 2 And Target.Row > 1 Or Target.Column = 5 And Target.Row > 1 Then
lr = Range("B" & Rows.Count).End(xlUp).Row
Range("G9:G" & lr).FormulaR1C1 = "=RC[-5]+R[-1]C-RC[-2]"
'if  you  don't  show  formula in column G then enable this  line 
'Range("G9:G" & lr).Value=Range("G9:G" & lr).Value
End If
Application.EnableEvents = True
End Sub

I hope this help you.

 

[kvemperor]

@kvemperor
I'm not sure if I understand your OP
but I supposes in G8 will contain value and from G9 will add formula . if you want just showing value not formula in column G ,then enable the line disabled as in comment inside the code
so this my try .
copy the code and paste into sheet module , not standard module

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Or Target.Row < 1 Then Exit Sub
Application.EnableEvents = False
If Target.Column = 2 And Target.Row > 1 Or Target.Column = 5 And Target.Row > 1 Then
lr = Range("B" & Rows.Count).End(xlUp).Row
Range("G9:G" & lr).FormulaR1C1 = "=RC[-5]+R[-1]C-RC[-2]"
'if  you  don't  show  formula in column G then enable this  line
'Range("G9:G" & lr).Value=Range("G9:G" & lr).Value
End If
Application.EnableEvents = True
End Sub

I hope this help you.

Thank you very much my friend for your answer. I will try it tomorrow and I will let you know

 

[kvemperor]

@kvemperor
I'm not sure if I understand your OP
but I supposes in G8 will contain value and from G9 will add formula . if you want just showing value not formula in column G ,then enable the line disabled as in comment inside the code
so this my try .
copy the code and paste into sheet module , not standard module

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Or Target.Row < 1 Then Exit Sub
Application.EnableEvents = False
If Target.Column = 2 And Target.Row > 1 Or Target.Column = 5 And Target.Row > 1 Then
lr = Range("B" & Rows.Count).End(xlUp).Row
Range("G9:G" & lr).FormulaR1C1 = "=RC[-5]+R[-1]C-RC[-2]"
'if  you  don't  show  formula in column G then enable this  line
'Range("G9:G" & lr).Value=Range("G9:G" & lr).Value
End If
Application.EnableEvents = True
End Sub

I hope this help you.

Hallo my friend.i tried this but something is wrong and i can not do it properly.