Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move line as Worksheet change not working correctly
Hi,
I have the following code that I found at http://www.eggheadcafe.com/community...5/cut-row.aspx Private Sub Worksheet_Change(ByVal Target As Range) If Left(Target.AddressLocal(ColumnAbsolute:=False), 1) = "M" Then InsPos = Sheets("Archive").Range("a65536").End(xlUp).Row + 1 Rows(Target.Row).Copy Sheets("Archive").Rows(InsPos) Rows(Target.Row).Delete shift:=xlUp End If End Sub This works when I enter a value in column "M", but if I chnage the reference to column AH it does not work (nothing happens at all, no errors, no cut-and-past). CAn anyone explain why this is happening (so that I understand) and et me know how to modify the code so that the cut-and-paste happens when a value is entered in column AH? TIA Dave |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move line as Worksheet change not working correctly
This is surely because in the Left() function you're looking at the first
character only, which can never be equal to "AH". Why not use: If Target.column = 13 ... [or for AH, 34] "Risky Dave" wrote: Hi, I have the following code that I found at http://www.eggheadcafe.com/community...5/cut-row.aspx Private Sub Worksheet_Change(ByVal Target As Range) If Left(Target.AddressLocal(ColumnAbsolute:=False), 1) = "M" Then InsPos = Sheets("Archive").Range("a65536").End(xlUp).Row + 1 Rows(Target.Row).Copy Sheets("Archive").Rows(InsPos) Rows(Target.Row).Delete shift:=xlUp End If End Sub This works when I enter a value in column "M", but if I chnage the reference to column AH it does not work (nothing happens at all, no errors, no cut-and-past). CAn anyone explain why this is happening (so that I understand) and et me know how to modify the code so that the cut-and-paste happens when a value is entered in column AH? TIA Dave |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move line as Worksheet change not working correctly
aardvark & Don
My thanks - fixed "Don Guillett" wrote: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 34 Then Exit Sub 'col AH is col 34 With Sheets("archive") lr = .Cells(Rows.Count, 1).End(xlUp).Row + 1 Rows(Target.Row).Cut .Rows(lr) Rows(Target.Row).Delete End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Risky Dave" wrote in message ... Hi, I have the following code that I found at http://www.eggheadcafe.com/community...5/cut-row.aspx Private Sub Worksheet_Change(ByVal Target As Range) If Left(Target.AddressLocal(ColumnAbsolute:=False), 1) = "M" Then InsPos = Sheets("Archive").Range("a65536").End(xlUp).Row + 1 Rows(Target.Row).Copy Sheets("Archive").Rows(InsPos) Rows(Target.Row).Delete shift:=xlUp End If End Sub This works when I enter a value in column "M", but if I chnage the reference to column AH it does not work (nothing happens at all, no errors, no cut-and-past). CAn anyone explain why this is happening (so that I understand) and et me know how to modify the code so that the cut-and-paste happens when a value is entered in column AH? TIA Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change worksheet, two trigger cells on one worksheet, only one triggers correctly; using two If statements, do I need End If? | Excel Programming | |||
how do i change the top line of worksheet? | Excel Discussion (Misc queries) | |||
Worksheet change: move to next cell | Excel Programming | |||
Excel 2002 : How to move to the last line of the active worksheet | Excel Discussion (Misc queries) | |||
Change position of move or copy worksheet option in Excel | Excel Discussion (Misc queries) |