VBA cut and paste

[JSnow]

I'm using Excel 2003 and want to move data from a cell in column C to column
D (same row) if the data starts with a six. Here's my code thus far:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Whoops
If Target.Column = 3 Then 'column C
If Target.Row > 1 Then 'ignore row 1
Dim LResult As String
LResult = Left(Target.Value, 1)
If LResult = "6" Then
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
End If
End If
End If

Whoops:
Application.EnableEvents = True
End Sub

Nothing happens. The sheet just sits there and mocks me.

 

[Rick Rothstein]

I think you will have to use Target rather than Selection and ActiveCell...
since this is in the Change event, I believe the ActiveCell has moved on to
another cell (in accordance with your Option setting) when you keyed in the
Enter key.

 

[Don Guillett]

I always like the KISS idea

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Or Target.Row < 2 Then Exit Sub
If Left(Target, 1) = "6" Then Target.Cut Target.Offset(, 1)
End Sub

 

[Don Guillett]

I tested your code and it worked

 

[Mike H]

Try it this way

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
If Target.Column = 3 And Target.Row > 1 Then 'ignore row 1
Application.EnableEvents = False
Dim LResult As String
If Left(Target.Value, 1) = "6" Then
Target.Offset(, 1).Value = Target.Value
Target.Value = ""
End If
End If
Whoops:
Application.EnableEvents = True
End Sub

Mike

 

[Rick Rothstein]

It didn't work for me.

--
Rick (MVP - Excel)

I tested your code and it worked

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Bernard Liengme]

Small modification

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LResult As String
Dim HoldRow As Integer
Application.EnableEvents = False
On Error GoTo Whoops
HoldRow = Target.Row
If Target.Column = 3 Then 'column C
If Target.Row > 1 Then 'ignore row 1
LResult = Left(Target.Value, 1)
If LResult = "6" Then
Cells(HoldRow, 4) = LResult
Cells(HoldRow, 3).ClearContents
End If
End If
End If

Whoops:
Application.EnableEvents = True
End Sub

best wishes

 

[JSnow]

BRILLIANT! Thanks so much.

Try it this way

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
If Target.Column = 3 And Target.Row > 1 Then 'ignore row 1
Application.EnableEvents = False
Dim LResult As String
If Left(Target.Value, 1) = "6" Then
Target.Offset(, 1).Value = Target.Value
Target.Value = ""
End If
End If
Whoops:
Application.EnableEvents = True
End Sub

Mike

 

[Mike H]

Don,

Agree about KISS but your code will execute twice because of the worksheet
change event when the cut value is pasted in, would you really recommend that?

Mike

 

[Mike H]

The OP's code will only work if you don't move cells on enter, see Rick's
comments on target

Mike

 

[Rick Rothstein]

One possible problem for you with Mike's solution... it doesn't duplicate
the Cut/Paste operation as Excel performs it. If the Target cell had
formatting, Mike's code leaves that formatting in place whereas Cut would
remove it. Also, Paste copies the formatting from the Cut cell over into the
new location whereas Mike's code doesn't.

The most direct route to do what you want is based on the code that Don
posted...

......
If Left(Target.Value, 1) = "6" Then
Target.Cut Target.Offset(, 1)
End If
......

but if you want to do it in two steps as Mike proposed, these steps would do
what Cut/Paste does...

......
If LResult = "6" Then
Target.Copy Target.Offset(0, 1)
Target.Clear
End If
......

 

[Mike H]

Rick,

Good point, I assumed (never safe to do) that the OP used cut & paste
because he didn't know any other methods.

Mike

 

[Rick Rothstein]

Notice I said "one **possible** problem". If the OP doesn't have any
formatting on the Target cell, then your code will work fine for him; I just
thought it important to point out the differences in how your code works and
how the OP was trying to do it... just in case it mattered later on,
perhaps.

 

[Don Guillett]

Rick, I just retested it and it worked as he had it written.
xl2003 vista HP

 

[Don Guillett]

Just re-tested and didn't notice...

 

[Rick Rothstein]

I am also using XL2003 on Vista (Ultimate edition though, but that shouldn't
matter). Do you have the "Move selection after Enter" checkbox unchecked on
the Tools/Options/Edit(tab) dialog? That would be the only way I can see the
OP's code working for you.

 

[Don Guillett]

Rick, I ALWAYS have that box checked.

 

[Don Guillett]

UN

 

[Mike H]

Don

In a implementation as trivial as this it's so fast you wouldn't notice but
try this and see how may times you have to tap F5 to exit the code

Private Sub Worksheet_Change(ByVal Target As Range)
Stop
If Target.Column <> 3 Or Target.Row < 2 Then Exit Sub
If Left(Target, 1) = "6" Then Target.Cut Target.Offset(, 1)
Stop
End Sub

Mike

 

[Rick Rothstein]

I'm guessing your two messages together are saying you do not have that
option checked. That would be why the OP's code works for you, but not for
me (I have the option checked). For you, the ActiveCell remains as the
Target cell after editing is complete... for me (and a lot of others out
there I would guess), the ActiveCell moves with the completion of editing
meaning that the ActiveCell is not the same as the Change event's Target
cell.