Move line as Worksheet change not working correctly

[Risky Dave]

Hi,

I have the following code that I found at
www.eggheadcafe.com/community/aspnet/66/10084345/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

 

[aardvark]

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]

 

[Don Guillett]

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

 

[Risky Dave]

aardvark & Don

My thanks - fixed

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
(e-mail address removed)