Log In
Register
Home
August 24, 2022
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Offline
No file attached, please make sure you press the Start Upload button after you use the Add Files to Queue button.
August 24, 2022
Offline
ok
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Offline
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
August 24, 2022
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Offline
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.
August 24, 2022
Offline
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
August 24, 2022
Offline
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
October 5, 2010
Offline
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
August 24, 2022
Offline
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?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Offline
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)
August 24, 2022
Offline
I need both of them!
Q9 enter and G10=G9-Q9 then G9=Q9
August 24, 2022
Offline
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?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Offline
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.
August 24, 2022
Offline
So
Can I use Vba code for unprotect sheet and do something then protect sheet?
Is it possible?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Offline
You can use Me.Unprotect at the beginning of the code, and Me.Protect at the end of code. (will be protected without a password)
But you have to make sure you have the target cells unlocked to allow changes in the cells that can trigger the code. If no cells are editable and unlocked, the code will not be triggered.
If you want to set a password:
Me.Unprotect Password:="MySecretPassword"
Me.Protect Password:="MySecretPassword"
