| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with macro
My goal is to add a cell between a name i enter and the total so there is
always a blank row. i have this but it only works if cell a4 is changed. A B c 1 Customers 1 George 2 Sam 3 4 Total Private Sub Worksheet_Change(ByVal Target As Range) Dim lastrow As Long, i As Long lastrow = Cells(Rows.Count, "a").End(xlUp).Row If Intersect(Target, Range("A4")) Is Nothing Then Exit Sub Else 'The cell you are monitoring is changed! For i = lastrow To 2 Step -1 If InStr(1, Cells(i, "a"), "Total", vbTextCompare) Then Rows(i).Insert End If Next Application.EnableEvents = True End If End Sub Any help would be appreciated |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with macro
Using your existing code this should work...
Private Sub Worksheet_Change(ByVal Target As Range) Dim lastrow As Long, i As Long lastrow = Cells(Rows.Count, "a").End(xlUp).Row If Target.Column = 1 Then 'The cell you are monitoring is changed! For i = lastrow To 2 Step -1 If InStr(1, Cells(i, "a"), "Total", vbTextCompare) Then Rows(i).Insert End If Next Application.EnableEvents = True End If End Sub -- HTH... Jim Thomlinson "jhyatt" wrote: My goal is to add a cell between a name i enter and the total so there is always a blank row. i have this but it only works if cell a4 is changed. A B c 1 Customers 1 George 2 Sam 3 4 Total Private Sub Worksheet_Change(ByVal Target As Range) Dim lastrow As Long, i As Long lastrow = Cells(Rows.Count, "a").End(xlUp).Row If Intersect(Target, Range("A4")) Is Nothing Then Exit Sub Else 'The cell you are monitoring is changed! For i = lastrow To 2 Step -1 If InStr(1, Cells(i, "a"), "Total", vbTextCompare) Then Rows(i).Insert End If Next Application.EnableEvents = True End If End Sub Any help would be appreciated |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with macro
problem it inserted blank rows all the way down to row 254
"Jim Thomlinson" wrote: Using your existing code this should work... Private Sub Worksheet_Change(ByVal Target As Range) Dim lastrow As Long, i As Long lastrow = Cells(Rows.Count, "a").End(xlUp).Row If Target.Column = 1 Then 'The cell you are monitoring is changed! For i = lastrow To 2 Step -1 If InStr(1, Cells(i, "a"), "Total", vbTextCompare) Then Rows(i).Insert End If Next Application.EnableEvents = True End If End Sub -- HTH... Jim Thomlinson "jhyatt" wrote: My goal is to add a cell between a name i enter and the total so there is always a blank row. i have this but it only works if cell a4 is changed. A B c 1 Customers 1 George 2 Sam 3 4 Total Private Sub Worksheet_Change(ByVal Target As Range) Dim lastrow As Long, i As Long lastrow = Cells(Rows.Count, "a").End(xlUp).Row If Intersect(Target, Range("A4")) Is Nothing Then Exit Sub Else 'The cell you are monitoring is changed! For i = lastrow To 2 Step -1 If InStr(1, Cells(i, "a"), "Total", vbTextCompare) Then Rows(i).Insert End If Next Application.EnableEvents = True End If End Sub Any help would be appreciated |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with macro
Your code never diables events. It re-nables but it never diabled in the
first place... Try this... Private Sub Worksheet_Change(ByVal Target As Range) Dim lastrow As Long, i As Long lastrow = Cells(Rows.Count, "a").End(xlUp).Row If Target.Column = 1 Then Application.EnableEvents = False 'The cell you are monitoring is changed! For i = lastrow To 2 Step -1 If InStr(1, Cells(i, "a"), "Total", vbTextCompare) Then Rows(i).Insert End If Next Application.EnableEvents = True End If End Sub -- HTH... Jim Thomlinson "jhyatt" wrote: problem it inserted blank rows all the way down to row 254 "Jim Thomlinson" wrote: Using your existing code this should work... Private Sub Worksheet_Change(ByVal Target As Range) Dim lastrow As Long, i As Long lastrow = Cells(Rows.Count, "a").End(xlUp).Row If Target.Column = 1 Then 'The cell you are monitoring is changed! For i = lastrow To 2 Step -1 If InStr(1, Cells(i, "a"), "Total", vbTextCompare) Then Rows(i).Insert End If Next Application.EnableEvents = True End If End Sub -- HTH... Jim Thomlinson "jhyatt" wrote: My goal is to add a cell between a name i enter and the total so there is always a blank row. i have this but it only works if cell a4 is changed. A B c 1 Customers 1 George 2 Sam 3 4 Total Private Sub Worksheet_Change(ByVal Target As Range) Dim lastrow As Long, i As Long lastrow = Cells(Rows.Count, "a").End(xlUp).Row If Intersect(Target, Range("A4")) Is Nothing Then Exit Sub Else 'The cell you are monitoring is changed! For i = lastrow To 2 Step -1 If InStr(1, Cells(i, "a"), "Total", vbTextCompare) Then Rows(i).Insert End If Next Application.EnableEvents = True End If End Sub Any help would be appreciated |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with macro
works great thanks jim
"Jim Thomlinson" wrote: Your code never diables events. It re-nables but it never diabled in the first place... Try this... Private Sub Worksheet_Change(ByVal Target As Range) Dim lastrow As Long, i As Long lastrow = Cells(Rows.Count, "a").End(xlUp).Row If Target.Column = 1 Then Application.EnableEvents = False 'The cell you are monitoring is changed! For i = lastrow To 2 Step -1 If InStr(1, Cells(i, "a"), "Total", vbTextCompare) Then Rows(i).Insert End If Next Application.EnableEvents = True End If End Sub -- HTH... Jim Thomlinson "jhyatt" wrote: problem it inserted blank rows all the way down to row 254 "Jim Thomlinson" wrote: Using your existing code this should work... Private Sub Worksheet_Change(ByVal Target As Range) Dim lastrow As Long, i As Long lastrow = Cells(Rows.Count, "a").End(xlUp).Row If Target.Column = 1 Then 'The cell you are monitoring is changed! For i = lastrow To 2 Step -1 If InStr(1, Cells(i, "a"), "Total", vbTextCompare) Then Rows(i).Insert End If Next Application.EnableEvents = True End If End Sub -- HTH... Jim Thomlinson "jhyatt" wrote: My goal is to add a cell between a name i enter and the total so there is always a blank row. i have this but it only works if cell a4 is changed. A B c 1 Customers 1 George 2 Sam 3 4 Total Private Sub Worksheet_Change(ByVal Target As Range) Dim lastrow As Long, i As Long lastrow = Cells(Rows.Count, "a").End(xlUp).Row If Intersect(Target, Range("A4")) Is Nothing Then Exit Sub Else 'The cell you are monitoring is changed! For i = lastrow To 2 Step -1 If InStr(1, Cells(i, "a"), "Total", vbTextCompare) Then Rows(i).Insert End If Next Application.EnableEvents = True End If End Sub Any help would be appreciated |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
| My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
| using a cell value to control a counter inside a macro and displaying macro value | Excel Worksheet Functions | |||
| Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
| Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Linear Mode
