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]

are you sure ?
this is what I got for your data

Microsoft Excel.xlsx
	A	B	C	D	E	F	G	H	I
7	ΗΜΕΡΑ	ΑΡ.ΣΥΝΝΑΛΑΓΗΣ ΤΙΜ	ΑΞΙΑ ΤΙΜ	ΑΡ.ΤΙΜ	ΑΡ.ΣΥΝΝΑΛΑΓΗΣ ENANTI	ΑΞΙΑ ENANTI	ΗΜΕΡΟΜΗΝΙΑ	ΠΙΣΤΩΣΗ/ΕΝΑΝΤΙ
8									11
9			11			22		0	0
10			123	Εκ μεταφορας		12	2022	0	111
1

Cell Formulas
Range	Formula
H8:H10	H8	=IF(AND(C8="",F8=""),"",IF(C8<>"",$L$2,$L$3))
I9:I10	I9	=C9+I8-F9

can you attach your file by dropbox to see what's the problem ?

 

[kvemperor]

are you sure ?
this is what I got for your data

Microsoft Excel.xlsx
	A	B	C	D	E	F	G	H	I
7	ΗΜΕΡΑ	ΑΡ.ΣΥΝΝΑΛΑΓΗΣ ΤΙΜ	ΑΞΙΑ ΤΙΜ	ΑΡ.ΤΙΜ	ΑΡ.ΣΥΝΝΑΛΑΓΗΣ ENANTI	ΑΞΙΑ ENANTI	ΗΜΕΡΟΜΗΝΙΑ	ΠΙΣΤΩΣΗ/ΕΝΑΝΤΙ
8									11
9			11			22		0	0
10			123	Εκ μεταφορας		12	2022	0	111
1

Cell Formulas
Range	Formula
H8:H10	H8	=IF(AND(C8="",F8=""),"",IF(C8<>"",$L$2,$L$3))
I9:I10	I9	=C9+I8-F9

can you attach your file by dropbox to see what's the problem ?

ΚΑΡΤΕΛΛΑ ΔΕΙΓΜΑ.xlsx

Shared with Dropbox

www.dropbox.com

this is the link.i believe i made it properly.

 

[abdelfattah]

why xlsx? you should attach file xlsm with the code .

 

[kvemperor]

why xlsx? you should attach file xlsm with the code .

ΚΑΡΤΕΛΛΑ ΔΕΙΓΜΑ.xlsm

Shared with Dropbox

www.dropbox.com

i am sorry.is this ok?

 

[abdelfattah]

cancel merging the columns and try this

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 = 3 And Target.Row > 8 Or Target.Column = 6 And Target.Row > 8Then
lr = Range("C" & Rows.Count).End(xlUp).Row
lr1 = Range("F" & Rows.Count).End(xlUp).Row
Range("I9:I" & lr & ":I9:I" & lr1).FormulaR1C1 = "=RC[-6]+R[-1]C-RC[-3]"
End If
Application.EnableEvents = True
End Sub

if it's doesn't work for you should wait else body . sorry !

 

[kvemperor]

cancel merging the columns and try this

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 = 3 And Target.Row > 7 Or Target.Column = 6 And Target.Row > 7 Then
lr = Range("C" & Rows.Count).End(xlUp).Row
lr1 = Range("F" & Rows.Count).End(xlUp).Row
Range("I9:I" & lr & ":I9:I" & lr1).FormulaR1C1 = "=RC[-6]+R[-1]C-RC[-3]"
End If
Application.EnableEvents = True
End Sub

if it's doesn't work for you should wait else body . sorry !

it doesn t work either. But i must thank you for all your effort. If anyone else doesn t succeed to make it then i will try also in other forums.if i find the solution i will post it also here(with the link of the other forum of course)for educational reasons.
Anyway thank you again for all your effort

 

[abdelfattah]

Hi
try this

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 = 3 And Target.Row > 8 Or Target.Column = 6 And Target.Row > 8 Then
Dim LR1 As Integer
LR1 = Range("F" & Rows.Count).End(xlUp).Row
For i = 9 To LR1
Range("I9:I" & i).FormulaR1C1 = "=RC[-6]+R[-1]C-RC[-3]"
Next
End If
Application.EnableEvents = True
End Sub

 

[kvemperor]

Hi
try this

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 = 3 And Target.Row > 8 Or Target.Column = 6 And Target.Row > 8 Then
Dim LR1 As Integer
LR1 = Range("F" & Rows.Count).End(xlUp).Row
For i = 9 To LR1
Range("I9:I" & i).FormulaR1C1 = "=RC[-6]+R[-1]C-RC[-3]"
Next
End If
Application.EnableEvents = True
End Sub

Hallo my friend again. I will try this one tomorrow and I let you know. I hope this time will work

 

[abdelfattah]

just question , is there any column always filled like date , if so which is ?

 

[kvemperor]

just question , is there any column always filled like date , if so which is ?

Yes in the column G I always put the current date( that I am putting values in the column C or the column F), and the column A is connected with column G with a function. Every time I put the date in the column G, in the column A I get the day (which is corresponding with the date I have put in the column G)