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

 

[I_know_nuffin]

try in G9

=if(F9="","",B9+G8-E9)

drag it down to all rows required

whenever you add anything in Col F it will update the result in col G

 

[abdelfattah]

yes you're right because the last row is just B as in the code
in reality I try doing without loop but I think showing error start from row8 . you can test it for two choices.

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
lr1 = Range("E" & Rows.Count).End(xlUp).Row
Range("G9:G" & lr & ":G9:G" & lr1).FormulaR1C1 = "=RC[-5]+R[-1]C-RC[-2]"
End If
Application.EnableEvents = True
End Sub

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("E" & Rows.Count).End(xlUp).Row
Range("G9:G" & lr ).FormulaR1C1 = "=RC[-5]+R[-1]C-RC[-2]"
End If
Application.EnableEvents = True
End Sub

I no know if there is idea to fix that by Alex or others members.
in this work , but with loop . you can also test it

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("E" & Rows.Count).End(xlUp).Row
For i = 9 To lr
Range("G9:G" & lr).FormulaR1C1 = "=RC[-5]+R[-1]C-RC[-2]"
Next i
'Range("G9:G" & lr).Value=Range("G9:G" & lr).Value
End If
Application.EnableEvents = True
End Sub

 

[kvemperor]

yes you're right because the last row is just B as in the code
in reality I try doing without loop but I think showing error start from row8 . you can test it for two choices.

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
lr1 = Range("E" & Rows.Count).End(xlUp).Row
Range("G9:G" & lr & ":G9:G" & lr1).FormulaR1C1 = "=RC[-5]+R[-1]C-RC[-2]"
End If
Application.EnableEvents = True
End Sub

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("E" & Rows.Count).End(xlUp).Row
Range("G9:G" & lr ).FormulaR1C1 = "=RC[-5]+R[-1]C-RC[-2]"
End If
Application.EnableEvents = True
End Sub

I no know if there is idea to fix that by Alex or others members.
in this work , but with loop . you can also test it

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("E" & Rows.Count).End(xlUp).Row
For i = 9 To lr
Range("G9:G" & lr).FormulaR1C1 = "=RC[-5]+R[-1]C-RC[-2]"
Next i
'Range("G9:G" & lr).Value=Range("G9:G" & lr).Value
End If
Application.EnableEvents = True
End Sub

The first option worked perfectly.You are honestly an expert in this.You helped me a lot. I didn't tried the other one you send me.
In the same worksheet i have put some extra columns.this is the photo of the new worksheet.if i change the name of the columns its going to work also perfectly like the first one?
thank you in advance for all your effort

 

[abdelfattah]

The first option worked perfectly.

I'm really surprised work for you . but good news(just make sure many times)
it doesn't work for me
and shows error from row9 and before it as in picture

ا1
	B	C	D	E	F	G
1						0
2						#REF!
3						#REF!
4						#REF!
5						#REF!
6						#REF!
7						#REF!
8						#REF!
9	1					#REF!
1

Cell Formulas
Range	Formula
G1	G1	=B1+G1048576-E1
G2:G9	G2	=B2+#REF!-E2

and if you works then should mark answer in post#12

In the same worksheet i have put some extra columns.this is the photo of the new worksheet.if i change the name of the columns its going to work also perfectly like the first one?

just try yourself and if you face a problem just post which columns and should use XL2BB tool instead of picture .

 

[kvemperor]

I'm really surprised work for you . but good news(just make sure many times)
it doesn't work for me
and shows error from row9 and before it as in picture

ا1
	B	C	D	E	F	G
1						0
2						#REF!
3						#REF!
4						#REF!
5						#REF!
6						#REF!
7						#REF!
8						#REF!
9	1					#REF!
1

Cell Formulas
Range	Formula
G1	G1	=B1+G1048576-E1
G2:G9	G2	=B2+#REF!-E2

and if you works then should mark answer in post#12

just try yourself and if you face a problem just post which columns and should use XL2BB tool instead of picture .

COPY ΚΑΡΤΕΛΛΑ ΔΕΙΓΜΑ.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									- €
12
13
14
15
16
17
18
19
20
ΕΤΑΙΡΙΑ 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:E20	E11	=IF(F11="","",COUNTA($F$11:F11))
B11:B20	B11	=IF(D11="","",COUNTA($D$11:D11))
H8:H20	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

This is my new worksheet.unfortunately it doesn t work with the code that i tried.i changed the letters but still no good result

 

[abdelfattah]

based on your picture means the entering of numbers in column C or F and the formula should be in column I .
as you said

The first option worked perfectly

try this modification and I make bold values ,columns and ranges to see what I modified

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[-5]+R[-1]C-RC[-2]"
End If
Application.EnableEvents = True
End Sub

 

[kvemperor]

based on your picture means the entering of numbers in column C or F and the formula should be in column I .
as you said

try this modification and I make bold values ,columns and ranges to see what I modified

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[-5]+R[-1]C-RC[-2]"
End If
Application.EnableEvents = True
End Sub

COPY ΚΑΡΤΕΛΛΑ ΔΕΙΓΜΑ.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			500,00 €					ΠΙΣΤΩΣΗ	#ΤΙΜΗ!
13					1	300,00 €		ΕΝΑΝΤΙ	#ΤΙΜΗ!
14			600,00 €					ΠΙΣΤΩΣΗ	#ΤΙΜΗ!
15					2	400,00 €		ΕΝΑΝΤΙ	#ΤΙΜΗ!
16
17
18
19
ΕΤΑΙΡΙΑ NEO TΕΛΕΥΤΑΙΟ (3)

Cell Formulas
Range	Formula
A11:A400	A11	=LET(x,G11:G400,IF(x="","",WEEKDAY(x)))
E11:E13,E15:E19	E11	=IF(F11="","",COUNTA($F$11:F11))
I7:I15	I7	=D7+I6-G7
B11:B19	B11	=IF(D11="","",COUNTA($D$11:D11))
H8:H19	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

First of all thank you very much for all your effort in general.it returns to me value(ΤΙΜΗ in greek means value)

 

[abdelfattah]

that's why I ask you

(just make sure many times)

as I said in post#14

and shows error from row9 and before it as in picture

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

 

[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