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]

Oops !
my bad !
I think now should work

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 = 3 And Target.Row > 1 Or Target.Column = 6 And Target.Row > 1 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

 

[kvemperor]

that's why I ask you

as I said in post#14

so can you tell me how works ? you should test again before you change the columns and show me the result .
I'm so confused

Oops !
my bad !
I think now should work

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 = 3 And Target.Row > 1 Or Target.Column = 6 And Target.Row > 1 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

still value error
This is before the code

ΚΑΡΤΕΛΛΑ ΔΕΙΓΜΑ.xlsx
	A	B	C	D	E	F	G	H	I
1	ONOMA
2
3
4
5
6
7	ΗΜΕΡΑ	ΑΡ.ΣΥΝΝΑΛΑΓΗΣ ΤΙΜ	ΑΞΙΑ ΤΙΜ	ΑΡ.ΤΙΜ	ΑΡ.ΣΥΝΝΑΛΑΓΗΣ ENANTI	ΑΞΙΑ ENANTI	ΗΜΕΡΟΜΗΝΙΑ	ΠΙΣΤΩΣΗ/ΕΝΑΝΤΙ	ΝΕΟ ΥΠΟΛΟΙΠΟ
8
9
10				Εκ μεταφορας			2022		- €
11			500,00 €					ΠΙΣΤΩΣΗ	500,00 €
12					1	1.000,00 €		ΕΝΑΝΤΙ
13			600,00 €					ΠΙΣΤΩΣΗ
14						20,00 €		ΕΝΑΝΤΙ
15
16
17
ΕΤΑΙΡΙΑ NEO TΕΛΕΥΤΑΙΟ (3)

Cell Formulas
Range	Formula
A11:A400	A11	=LET(x,G11:G400,IF(x="","",WEEKDAY(x)))
I10:I11	I10	=C10+I9-F10
E11:E13,E15:E17	E11	=IF(F11="","",COUNTA($F$11:F11))
B11:B17	B11	=IF(D11="","",COUNTA($D$11:D11))
H8:H17	H8	=IF(AND(C8="",F8=""),"",IF(C8<>"",$L$2,$L$3))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H8:H399	Expression	=H8=$L$3	text	NO
H8:H399	Expression	=H8=$L$2	text	NO

and this is after the code the result

ΚΑΡΤΕΛΛΑ ΔΕΙΓΜΑ.xlsx
	A	B	C	D	E	F	G	H	I
1	ONOMA
2
3
4
5
6
7	ΗΜΕΡΑ	ΑΡ.ΣΥΝΝΑΛΑΓΗΣ ΤΙΜ	ΑΞΙΑ ΤΙΜ	ΑΡ.ΤΙΜ	ΑΡ.ΣΥΝΝΑΛΑΓΗΣ ENANTI	ΑΞΙΑ ENANTI	ΗΜΕΡΟΜΗΝΙΑ	ΠΙΣΤΩΣΗ/ΕΝΑΝΤΙ	#ΤΙΜΗ!
8									#ΤΙΜΗ!
9									#ΤΙΜΗ!
10				Εκ μεταφορας			2022		#ΤΙΜΗ!
11			500,00 €					ΠΙΣΤΩΣΗ	#ΤΙΜΗ!
12					1	1.000,00 €		ΕΝΑΝΤΙ	#ΤΙΜΗ!
13			600,00 €					ΠΙΣΤΩΣΗ	#ΤΙΜΗ!
14						20,00 €		ΕΝΑΝΤΙ	#ΤΙΜΗ!
15
16
17
18
ΕΤΑΙΡΙΑ NEO TΕΛΕΥΤΑΙΟ (3)

Cell Formulas
Range	Formula
A11:A400	A11	=LET(x,G11:G400,IF(x="","",WEEKDAY(x)))
E11:E13,E15:E18	E11	=IF(F11="","",COUNTA($F$11:F11))
I7:I14	I7	=C7+I6-F7
B11:B18	B11	=IF(D11="","",COUNTA($D$11:D11))
H8:H18	H8	=IF(AND(C8="",F8=""),"",IF(C8<>"",$L$2,$L$3))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H8:H399	Expression	=H8=$L$3	text	NO
H8:H399	Expression	=H8=$L$2	text	NO

somehow it gives value error and from the beginning of the column I

 

[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