| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
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("B7  65000")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 |
|
|
|
|||
|
|
| |
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)... > 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("B7 65000")> 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 > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
On Feb 14, 4:20 am, "Bob Phillips" <bob....@somewhere.com> wrote:
> 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" <jamesf...@earthlink.net> wrote in message > > news:(E-Mail Removed)... > > > > > 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("B7 65000")> > 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- Hide quoted text - > > - 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 |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)... > On Feb 14, 4:20 am, "Bob Phillips" <bob....@somewhere.com> wrote: > > 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" <jamesf...@earthlink.net> wrote in message > > > > news:(E-Mail Removed)... > > > > > > > > > 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("B7 65000")> > > 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- Hide quoted text - > > > > - 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 > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
On Feb 14, 8:35 am, "Bob Phillips" <bob....@somewhere.com> wrote:
> 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" <jamesf...@earthlink.net> wrote in message > > news:(E-Mail Removed)... > > > On Feb 14, 4:20 am, "Bob Phillips" <bob....@somewhere.com> wrote: > > > 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" <jamesf...@earthlink.net> wrote in message > > > >news:(E-Mail Removed)... > > > > > 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("B7 65000")> > > > 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- Hide quoted text - > > > > - 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 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 |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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 -- Regards, Tom Ogilvy "jfcby" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)... > On Feb 14, 8:35 am, "Bob Phillips" <bob....@somewhere.com> wrote: >> 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" <jamesf...@earthlink.net> wrote in message >> >> news:(E-Mail Removed)... >> >> > On Feb 14, 4:20 am, "Bob Phillips" <bob....@somewhere.com> wrote: >> > > 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" <jamesf...@earthlink.net> wrote in message >> >> > >news:(E-Mail Removed)... >> >> > > > 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("B7 65000")>> > > > 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- Hide quoted text - >> >> > > - 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 > > 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 > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
On Feb 14, 10:48 pm, "Tom Ogilvy" <twogi...@msn.com> wrote:
> 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 > > -- > Regards, > Tom Ogilvy > > "jfcby" <jamesf...@earthlink.net> wrote in message > > news:(E-Mail Removed)... > > > On Feb 14, 8:35 am, "Bob Phillips" <bob....@somewhere.com> wrote: > >> 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" <jamesf...@earthlink.net> wrote in message > > >>news:(E-Mail Removed)... > > >> > On Feb 14, 4:20 am, "Bob Phillips" <bob....@somewhere.com> wrote: > >> > > 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" <jamesf...@earthlink.net> wrote in message > > >> > >news:(E-Mail Removed)... > > >> > > > 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("B7 65000")> >> > > > 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- Hide quoted text - > > >> > > - 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 > > > 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 Hello All Responsers, Thank you for your help. Problem was solved with Tom's response. jfcby |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Paste and Paste Special No Longer Working - Excel 2003 | SheriJ | Microsoft Excel Misc | 2 | 15th Jan 2009 09:23 PM |
| HELP - Excel 2003 cut, copy, paste and paste special problems | Carrie | Microsoft Excel Crashes | 2 | 9th Dec 2008 06:36 PM |
| Excel 2000 copy, edit, paste spacial, value. How Excel 2007 ?? | Boaz Amir | Microsoft Excel Misc | 1 | 30th Apr 2008 10:15 PM |
| Sum Left Formula, Excel 2000 & 2003 | jfcby | Microsoft Excel Programming | 2 | 30th Oct 2007 03:55 PM |
| Word 2000 paste special links from Excel 2000 | =?Utf-8?B?UGV0ZSBX?= | Microsoft Word Document Management | 5 | 18th Jun 2004 02:29 PM |
