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

 

[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.

 

[abdelfattah]

as I said

so this my try .

here is the picture when enter values whether in column B or E start from row 9 and add value in G8 to see the changes in column G

formula.xlsm
	B	C	D	E	F	G
8						1
9	7			6		2
10	12			11		3
11	11			11		3
12	1			11		-7
st

Cell Formulas
Range	Formula
G9:G12	G9	=B9+G8-E9

 

[kvemperor]

as I said


here is the picture when enter values whether in column B or E start from row 9 and add value in G8 to see the changes in column G

formula.xlsm
	B	C	D	E	F	G
8						1
9	7			6		2
10	12			11		3
11	11			11		3
12	1			11		-7
st

Cell Formulas
Range	Formula
G9:G12	G9	=B9+G8-E9

is working with column B but not with column E.this is a screenshot of my excel