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]

OMG !!

when i put a value in the column G.Its like i keep an order when i am putting the values in the columns E or G

just I need to this . you adjusted column date entering from G to B , then will change values entering from columns C,F to E,G, right?

 

[kvemperor]

OMG !!

just I need to this . you adjusted column date entering from G to B , then will change values entering from columns C,F to E,G, right?

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

 

[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

 

[kvemperor]

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



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

I apologize for any misunderstanding. You did already a lot for me. I will try this tomorrow (it's here night time here in Greece). I let you know when I try this. Again sorry for any misunderstanding

 

[abdelfattah]

I will try this tomorrow (it's here night time here in Greece)

no problem
just make sure enter in column B firstly , enter in column E or G .
and if work for you don't forget mark answering .

 

[kvemperor]

no problem
just make sure enter in column B firstly , enter in column E or G .
and if work for you don't forget mark answering .

Hallo my friend.Still no working

ΚΑΡΤΕΛΛΑ ΔΕΙΓΜΑ.xlsm
	A	B	C	D	E	F	G	H	I
1	ONOMA
2
3
4
5
6
7	ΗΜΕΡΑ	ΗΜΕΡΟΜΗΝΙΑ	ΑΡ.ΣΥΝΝΑΛΑΓΗΣ ΤΙΜ	ΑΞΙΑ ΤΙΜ	ΑΡ.ΤΙΜ	ΑΡ.ΣΥΝΝΑΛΑΓΗΣ ENANTI	ΑΞΙΑ ENANTI	ΠΙΣΤΩΣΗ/ΕΝΑΝΤΙ	ΝΕΟ ΥΠΟΛΟΙΠΟ
8
9									- €
10		2022			Εκ μεταφορας				- €
11	Τετάρτη	01/2/2023	1	6.000,00 €	TIM 6			ΠΙΣΤΩΣΗ	#ΤΙΜΗ!
12	Πέμπτη	02/2/2023	2	3.000,00 €	TIM 7			ΠΙΣΤΩΣΗ	#ΤΙΜΗ!
13
14
15
16
17
ΕΤΑΙΡΙΑ NEO TΕΛΕΥΤΑΙΟ (3)

Cell Formulas
Range	Formula
A11:A400	A11	=LET(x,B11:B400,IF(x="","",WEEKDAY(x)))
I9:I12	I9	=C9+I8-F9
F11:F13,F15:F17	F11	=IF(G11="","",COUNTA($G$11:G11))
C11:C17	C11	=IF(E11="","",COUNTA($E$11:E11))
H8:H17	H8	=IF(AND(D8="",G8=""),"",IF(D8<>"",$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

 

[I_know_nuffin]

add

Target.Column = 2 And Target.Row > 8 or

to the if statement

 

[kvemperor]

add

Target.Column = 2 And Target.Row > 8 or

to the if statement

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Column = 2 And Target.Row > 8 or
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

Like that?

 

[I_know_nuffin]

no here

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 = 2 And Target.Row > 8) or (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 .