Hi,
I have a excel with this code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 12 Or Target.Columns.Count > 1 Then _
If Target.Column <> 14 Or Target.Columns.Count > 1 Then _
If Target.Column <> 16 Or Target.Columns.Count > 1 Then _
Exit Sub
Dim tmp As Variant
tmp = Cells(Target.Row, 16).Formula 'save contents
On Error GoTo Enable_Events
Application.EnableEvents = False
Cells(Target.Row, 16) = "#$"
Range("A1").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
Cells(Application.Match("#$", Columns(16), 0), 1).Select
Range("L1").Sort Key1:=Range("L1"), Order1:=xlAscending, Header:=xlYes
Cells(Application.Match("#$", Columns(16), 0), 12).Select
Range("N1").Sort Key1:=Range("N1"), Order1:=xlAscending, Header:=xlYes
Cells(Application.Match("#$", Columns(16), 0), 14).Select
Range("P1").Sort Key1:=Range("P1"), Order1:=xlDescending, Header:=xlYes
Cells(Application.Match("#$", Columns(16), 0), 16).Select
Cells(Selection.Row, 16) = tmp 'restore contents
Enable_Events:
Application.EnableEvents = True
End Sub
now, I want to create new column, like this:
if Q1<G1, then insert new row below, then copy all formatting and numbers there, and G2=G1-Q1)
How can I use something like that in the Vba code?
I attached file here
No file attached, please make sure you press the Start Upload button after you use the Add Files to Queue button.
ok
The code you have is triggered by Worksheet Change event.
You want to insert new rows based on sheet changes as well?
If column Q should insert rows, then the code should look like this:
If Not Intersect(Target, Range("Q2:Q1000")) Is Nothing Then
If Cells(Target.Row, "G") <> 0 And Target < Cells(Target.Row, "G") Then
Target.Offset(1, 0).EntireRow.Insert
Cells(Target.Row, "G").Offset(1, 0).Formula = "=" & Cells(Target.Row, "G").Address(False, False) & "-" & Target.Address(False, False)
End If
End If
You should also allow column Q to trigger the code:
If Target.Column <> 12 Or Target.Columns.Count > 1 Then _
If Target.Column <> 14 Or Target.Columns.Count > 1 Then _
If Target.Column <> 17 Or Target.Columns.Count > 1 Then _
Exit Sub
appreciate for answering me
Look at this file:
I always use Q column for green rows, when I enter the quantity less than G column, I want to insert row below that cells
now it happened at the end and newG=Gup-Qup (G10=G9-Q9) but for example: I enter in Q4, so new row insert in below, and G5=G4-Q4, and all information in row 4 copy to row 5
Ok, added code to copy row formats and values:
If Cells(Target.Row, "G") <> 0 And Target < Cells(Target.Row, "G") Then
Target.Offset(1, 0).EntireRow.Insert
Range("A" & Target.Row & ":Q" & Target.Row).Copy _
Destination:=Range("A" & Target.Row + 1 & ":Q" & Target.Row + 1)
Cells(Target.Row, "G").Offset(1, 0).Formula = "=" & Cells(Target.Row, "G").Address(False, False) & "-" & Target.Address(False, False)
End If
End If
The part in red is the only addition.
I always use Q column for green rows, when I enter the quantity less than G column, I want to insert row below that cells
now it happened at the end and newG=Gup-Qup (G10=G9-Q9) but for example: I enter in Q4, so new row insert in below, and G5=G4-Q4, and all information in row 4 copy to row 5
Please keep in mind that the filtering you do after inserting the row is what pushes down the new row, the row is properly inserted below the target row.
Thank you
I add that code and look at the video attached
when I enter the quantity in Q cell, it is repeated in copy passed cell too. I want not to repeated there again
I can not uploaded video here
when I change in L column to "الصاق شد" then, I want to enter quantity in Q column. now when I enter in Q column, the quantity repeated in new row. I do not the Q cell repeated
Hi Neda,
You can use Loom to record your screen and host the video then link to it from here. If we hosted everyone's videos we'd run out of disk space very quickly.
regards
Phil
I do it.
in the code: Range("A" & Target.Row & ":Q" & Target.Row).Copy _
I use P instead of Q and solved!
Now, I want it:
for example, enter Q9 , then insert new row below and information copy and G10=G9-Q9, now I want G9=Q9
how can do this?
The formula below means G10=G9-Q9:
Cells(Target.Row, "G").Offset(1, 0).Formula = "=" & Cells(Target.Row, "G").Address(False, False) & "-" & Target.Address(False, False)
This one means G9=Q9:
Cells(Target.Row, "G").Formula = "=" & Target.Address(False, False)
I need both of them!
Q9 enter and G10=G9-Q9 then G9=Q9
I have another question too,
this excel is in the share and the sheet and the workbook is protected, so the code is not available?
I need both of them!
then use both codes provided.
this excel is in the share and the sheet and the workbook is protected, so the code is not available?
Code works in Excel desktop, not in Excel Online.
So
Can I use Vba code for unprotect sheet and do something then protect sheet?
Is it possible?
