Cut/Paste using Left(), Excel 2000 & 2003

[jfcby]

Hello,

Beginning with row4 in ColumnE my data has CO125. I want to cut the CO
and paste it in ColumnF.

This is the macro I've got so far:

Sub DeleteCharacters()
'
Dim strS, strC
Set Rng = Range("B765000")
For Each cell In Rng
strS = "CO"
strC = Left(cell, 2)
If strC = strS Then
strC.Select
Selection.Cut
ElseIf cell.Value = "" Then
Exit Sub
End If
Next
End Sub

Thank you for your help,
jfcby

 

[Bob Phillips]

Sub DeleteCharacters()
'
Dim strS
Set Rng = Range("E7:E65000")
For Each Cell In Rng
strS = "CO"
If Left(Cell, 2) = strS Then
strC.Cut .Cell.Offset(0, 1)
ElseIf Cell.Value = "" Then
Exit Sub
End If
Next
End Sub


--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[jfcby]

Sub DeleteCharacters()
'
Dim strS
Set Rng = Range("E7:E65000")
For Each Cell In Rng
strS = "CO"
If Left(Cell, 2) = strS Then
strC.Cut .Cell.Offset(0, 1)
ElseIf Cell.Value = "" Then
Exit Sub
End If
Next
End Sub

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)










- Show quoted text -

Hello Bob,

When I tried your code it give me this error message:

Compile error: syntax error

Then it highlights this line of code:
strC.Cut.cell.Offset(0, 1)

I changed it to this and it gave me the same error:
Left(cell, 2).Cut.cell.Offset(0, 1)

Thank you for your help,
jfcby

 

[Bob Phillips]

Sorry, my error

Sub DeleteCharacters()
'
Dim strS As String
Dim rng As Range
Dim cell As Range
Set rng = Range("E7:E65000")
strS = "CO"
For Each cell In rng
If Left(cell, 2) = strS Then
cell.Cut cell.Offset(0, 1)
ElseIf cell.Value = "" Then
Exit Sub
End If
Next
End Sub

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[jfcby]

Sorry, my error

Sub DeleteCharacters()
'
Dim strS As String
Dim rng As Range
Dim cell As Range
Set rng = Range("E7:E65000")
strS = "CO"
For Each cell In rng
If Left(cell, 2) = strS Then
cell.Cut cell.Offset(0, 1)
ElseIf cell.Value = "" Then
Exit Sub
End If
Next
End Sub

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Hello Bob,

When I ran your code it moves the data from ColumnE ActiveCell to the
right. I need it to move only the CO to the right:

Example:
ColumnE
CO125
CO126
CO127

ColumnE ColumnF
125 CO
126 CO
127 CO

Thank you for your help,
jfcby

 

[Tom Ogilvy]

Sub DeleteCharacters()
'
Dim strS As String
Dim rng As Range
Dim cell As Range
Set rng = Range("E7:E65000")
strS = "CO"
For Each cell In rng
If Left(cell, 2) = strS Then
cell.Offset(0, 1) = strS
cell.Value = Replace(cell.Value,strS,"")
ElseIf cell.Value = "" Then
Exit Sub
End If
Next
End Sub

 

[jfcby]

Sub DeleteCharacters()
'
Dim strS As String
Dim rng As Range
Dim cell As Range
Set rng = Range("E7:E65000")
strS = "CO"
For Each cell In rng
If Left(cell, 2) = strS Then
cell.Offset(0, 1) = strS
cell.Value = Replace(cell.Value,strS,"")
ElseIf cell.Value = "" Then
Exit Sub
End If
Next
End Sub

Hello All Responsers,

Thank you for your help. Problem was solved with Tom's response.

jfcby