Let me re-ask my question perhaps more intelligently and more fully. My goal
is to scan a spreadsheet with data in many columns but in many cases the data
is in the wrong column. The data all starts with a string which easily
identifies which column it should go into. For example if my columns were
Fruit, Cars, People, the data would be Fruit: apply, or Cars: Chevy, or
People: Men. So I need a code which looks at each cell for a certain word
and place that whole cell's contents/string into the right column (which I
would rather set aside away as an extra column). The ROW should stay the
SAME though since the other rows/columns may be okay? (unless I just write a
code to move it all (which would be nice). Hope that was clear. here's what
I have that doesn't work. Any help would be very greatly appreciated as I
continue to read and learn early in this new "career."

Sub Macro1()

Set myrange = ActiveSheet.Range(Selection, Selection.End(xlUp))
For Each cell In myrange
If Left(cell.Value, 5) = "GL Op" Then
cell.Cut
cell.Paste ("GL Op")
ElseIf Left(cell.Value, 5) = "Au Op" Then
cell.Cut
cell.Paste ("Au Op")
ElseIf Left(cell.Value, 5) = "WC Op" Then
cell.Cut
cell.Paste ("WC Op")
ElseIf Left(cell.Value, 5) = "CA Op" Then
cell.Cut
cell.Paste ("Au Op")
End If
Next cell

End Sub

I can't figure out what you want. If desired, send your file to my address
below along with a complete explanation and before/after examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"mypetduke" wrote in message
...
Let me re-ask my question perhaps more intelligently and more fully. My
goal
is to scan a spreadsheet with data in many columns but in many cases the
data
is in the wrong column. The data all starts with a string which easily
identifies which column it should go into. For example if my columns were
Fruit, Cars, People, the data would be Fruit: apply, or Cars: Chevy, or
People: Men. So I need a code which looks at each cell for a certain word
and place that whole cell's contents/string into the right column (which I
would rather set aside away as an extra column). The ROW should stay the
SAME though since the other rows/columns may be okay? (unless I just write
a
code to move it all (which would be nice). Hope that was clear. here's
what
I have that doesn't work. Any help would be very greatly appreciated as I
continue to read and learn early in this new "career."

Sub Macro1()

Set myrange = ActiveSheet.Range(Selection, Selection.End(xlUp))
For Each cell In myrange
If Left(cell.Value, 5) = "GL Op" Then
cell.Cut
cell.Paste ("GL Op")
ElseIf Left(cell.Value, 5) = "Au Op" Then
cell.Cut
cell.Paste ("Au Op")
ElseIf Left(cell.Value, 5) = "WC Op" Then
cell.Cut
cell.Paste ("WC Op")
ElseIf Left(cell.Value, 5) = "CA Op" Then
cell.Cut
cell.Paste ("Au Op")
End If
Next cell

End Sub

I thought this would be easiest if you inserted a blank row to rearrange the
first record row into the create a new blank row where your just rearranged
row was, and loop through the rows in your recordset. This eliminates the
possibility of overwriting something you didn't intend to.

Assumptions: Header/Title row in row 1; data beginning in row 2 without
"gap" rows. Your "Selection.End(xlUp)" construction seems to assume no gaps
as well. This puts GL in column A, Au in column B, etc... puts anything else
including blank cells in column E, which of course you could rearrange as you
please. See what you think...

Sub MyDataColumnsArranger()

Dim MyVal As String
Dim r As Integer, c As Integer, i As Integer

Rows(2).Insert shift:=xlDown

r = Range("a3").End(xlDown)
For r = 3 To r
For i = 1 To 4
MyVal = Left(Cells(r, i), 2)
Select Case MyVal
Case "GL": c = 1
Case "Au": c = 2
Case "WC": c = 3
Case "CA": c = 4
End Select
Cells(r - 1, c).Value = MyVal
Next i
Rows(r).ClearContents
Next r

End Sub


"mypetduke" wrote:

Let me re-ask my question perhaps more intelligently and more fully. My goal
is to scan a spreadsheet with data in many columns but in many cases the data
is in the wrong column. The data all starts with a string which easily
identifies which column it should go into. For example if my columns were
Fruit, Cars, People, the data would be Fruit: apply, or Cars: Chevy, or
People: Men. So I need a code which looks at each cell for a certain word
and place that whole cell's contents/string into the right column (which I
would rather set aside away as an extra column). The ROW should stay the
SAME though since the other rows/columns may be okay? (unless I just write a
code to move it all (which would be nice). Hope that was clear. here's what
I have that doesn't work. Any help would be very greatly appreciated as I
continue to read and learn early in this new "career."

Sub Macro1()

Set myrange = ActiveSheet.Range(Selection, Selection.End(xlUp))
For Each cell In myrange
If Left(cell.Value, 5) = "GL Op" Then
cell.Cut
cell.Paste ("GL Op")
ElseIf Left(cell.Value, 5) = "Au Op" Then
cell.Cut
cell.Paste ("Au Op")
ElseIf Left(cell.Value, 5) = "WC Op" Then
cell.Cut
cell.Paste ("WC Op")
ElseIf Left(cell.Value, 5) = "CA Op" Then
cell.Cut
cell.Paste ("Au Op")
End If
Next cell

End Sub

Oops, a slight error there - sorry. You'd need one more string variable to
hold the full value of the cell being evaluated evaluating for placement in
row above. Code below edited accordingly.

"slarbie" wrote:

I thought this would be easiest if you inserted a blank row to rearrange the
first record row into the create a new blank row where your just rearranged
row was, and loop through the rows in your recordset. This eliminates the
possibility of overwriting something you didn't intend to.

Assumptions: Header/Title row in row 1; data beginning in row 2 without
"gap" rows. Your "Selection.End(xlUp)" construction seems to assume no gaps
as well. This puts GL in column A, Au in column B, etc... puts anything else
including blank cells in column E, which of course you could rearrange as you
please. See what you think...

Sub MyDataColumnsArranger()

Dim MyVal As String, FullVal As String
Dim r As Integer, c As Integer, i As Integer

Rows(2).Insert shift:=xlDown

r = Range("a3").End(xlDown)
For r = 3 To r
For i = 1 To 4
FullVal = cells(r, i)
MyVal = Left(Cells(r, i), 2)
Select Case MyVal
Case "GL": c = 1
Case "Au": c = 2
Case "WC": c = 3
Case "CA": c = 4
End Select
Cells(r - 1, c).Value = FullVal
Next i
Rows(r).ClearContents
Next r

End Sub


"mypetduke" wrote:

Let me re-ask my question perhaps more intelligently and more fully. My goal
is to scan a spreadsheet with data in many columns but in many cases the data
is in the wrong column. The data all starts with a string which easily
identifies which column it should go into. For example if my columns were
Fruit, Cars, People, the data would be Fruit: apply, or Cars: Chevy, or
People: Men. So I need a code which looks at each cell for a certain word
and place that whole cell's contents/string into the right column (which I
would rather set aside away as an extra column). The ROW should stay the
SAME though since the other rows/columns may be okay? (unless I just write a
code to move it all (which would be nice). Hope that was clear. here's what
I have that doesn't work. Any help would be very greatly appreciated as I
continue to read and learn early in this new "career."

Sub Macro1()

Set myrange = ActiveSheet.Range(Selection, Selection.End(xlUp))
For Each cell In myrange
If Left(cell.Value, 5) = "GL Op" Then
cell.Cut
cell.Paste ("GL Op")
ElseIf Left(cell.Value, 5) = "Au Op" Then
cell.Cut
cell.Paste ("Au Op")
ElseIf Left(cell.Value, 5) = "WC Op" Then
cell.Cut
cell.Paste ("WC Op")
ElseIf Left(cell.Value, 5) = "CA Op" Then
cell.Cut
cell.Paste ("Au Op")
End If
Next cell

End Sub

Thanks for all your time/help. Well, each time I execute, the whole group of
text cells moves down one row in unison but none of the cells move to their
respective columns or change columns at all. ??

"slarbie" wrote:

Oops, a slight error there - sorry. You'd need one more string variable to
hold the full value of the cell being evaluated evaluating for placement in
row above. Code below edited accordingly.

"slarbie" wrote:

I thought this would be easiest if you inserted a blank row to rearrange the
first record row into the create a new blank row where your just rearranged
row was, and loop through the rows in your recordset. This eliminates the
possibility of overwriting something you didn't intend to.

Assumptions: Header/Title row in row 1; data beginning in row 2 without
"gap" rows. Your "Selection.End(xlUp)" construction seems to assume no gaps
as well. This puts GL in column A, Au in column B, etc... puts anything else
including blank cells in column E, which of course you could rearrange as you
please. See what you think...

Sub MyDataColumnsArranger()

Dim MyVal As String, FullVal As String
Dim r As Integer, c As Integer, i As Integer

Rows(2).Insert shift:=xlDown

r = Range("a3").End(xlDown)
For r = 3 To r
For i = 1 To 4
FullVal = cells(r, i)
MyVal = Left(Cells(r, i), 2)
Select Case MyVal
Case "GL": c = 1
Case "Au": c = 2
Case "WC": c = 3
Case "CA": c = 4
End Select
Cells(r - 1, c).Value = FullVal
Next i
Rows(r).ClearContents
Next r

End Sub


"mypetduke" wrote:

Let me re-ask my question perhaps more intelligently and more fully. My goal
is to scan a spreadsheet with data in many columns but in many cases the data
is in the wrong column. The data all starts with a string which easily
identifies which column it should go into. For example if my columns were
Fruit, Cars, People, the data would be Fruit: apply, or Cars: Chevy, or
People: Men. So I need a code which looks at each cell for a certain word
and place that whole cell's contents/string into the right column (which I
would rather set aside away as an extra column). The ROW should stay the
SAME though since the other rows/columns may be okay? (unless I just write a
code to move it all (which would be nice). Hope that was clear. here's what
I have that doesn't work. Any help would be very greatly appreciated as I
continue to read and learn early in this new "career."

Sub Macro1()

Set myrange = ActiveSheet.Range(Selection, Selection.End(xlUp))
For Each cell In myrange
If Left(cell.Value, 5) = "GL Op" Then
cell.Cut
cell.Paste ("GL Op")
ElseIf Left(cell.Value, 5) = "Au Op" Then
cell.Cut
cell.Paste ("Au Op")
ElseIf Left(cell.Value, 5) = "WC Op" Then
cell.Cut
cell.Paste ("WC Op")
ElseIf Left(cell.Value, 5) = "CA Op" Then
cell.Cut
cell.Paste ("Au Op")
End If
Next cell

End Sub

apologies. was at work and wrote it in a hurry. just add ".row" to the one
line that finds the last row so it looks like this:

r = Range("a3").End(xlDown).row

and of course change the column numbers to be where your columns really
should be. Hope that helps.

"mypetduke" wrote:

Thanks for all your time/help. Well, each time I execute, the whole group of
text cells moves down one row in unison but none of the cells move to their
respective columns or change columns at all. ??

"slarbie" wrote:

Oops, a slight error there - sorry. You'd need one more string variable to
hold the full value of the cell being evaluated evaluating for placement in
row above. Code below edited accordingly.

"slarbie" wrote:

I thought this would be easiest if you inserted a blank row to rearrange the
first record row into the create a new blank row where your just rearranged
row was, and loop through the rows in your recordset. This eliminates the
possibility of overwriting something you didn't intend to.

Assumptions: Header/Title row in row 1; data beginning in row 2 without
"gap" rows. Your "Selection.End(xlUp)" construction seems to assume no gaps
as well. This puts GL in column A, Au in column B, etc... puts anything else
including blank cells in column E, which of course you could rearrange as you
please. See what you think...

Sub MyDataColumnsArranger()

Dim MyVal As String, FullVal As String
Dim r As Integer, c As Integer, i As Integer

Rows(2).Insert shift:=xlDown

r = Range("a3").End(xlDown)
For r = 3 To r
For i = 1 To 4
FullVal = cells(r, i)
MyVal = Left(Cells(r, i), 2)
Select Case MyVal
Case "GL": c = 1
Case "Au": c = 2
Case "WC": c = 3
Case "CA": c = 4
End Select
Cells(r - 1, c).Value = FullVal
Next i
Rows(r).ClearContents
Next r

End Sub


"mypetduke" wrote:

Let me re-ask my question perhaps more intelligently and more fully. My goal
is to scan a spreadsheet with data in many columns but in many cases the data
is in the wrong column. The data all starts with a string which easily
identifies which column it should go into. For example if my columns were
Fruit, Cars, People, the data would be Fruit: apply, or Cars: Chevy, or
People: Men. So I need a code which looks at each cell for a certain word
and place that whole cell's contents/string into the right column (which I
would rather set aside away as an extra column). The ROW should stay the
SAME though since the other rows/columns may be okay? (unless I just write a
code to move it all (which would be nice). Hope that was clear. here's what
I have that doesn't work. Any help would be very greatly appreciated as I
continue to read and learn early in this new "career."

Sub Macro1()

Set myrange = ActiveSheet.Range(Selection, Selection.End(xlUp))
For Each cell In myrange
If Left(cell.Value, 5) = "GL Op" Then
cell.Cut
cell.Paste ("GL Op")
ElseIf Left(cell.Value, 5) = "Au Op" Then
cell.Cut
cell.Paste ("Au Op")
ElseIf Left(cell.Value, 5) = "WC Op" Then
cell.Cut
cell.Paste ("WC Op")
ElseIf Left(cell.Value, 5) = "CA Op" Then
cell.Cut
cell.Paste ("Au Op")
End If
Next cell

End Sub

Now it says "run time error 6 overflow" and, of course I have no idea what
that means. But again I do appreciate your help.


"slarbie" wrote:

apologies. was at work and wrote it in a hurry. just add ".row" to the one
line that finds the last row so it looks like this:

r = Range("a3").End(xlDown).row

and of course change the column numbers to be where your columns really
should be. Hope that helps.

"mypetduke" wrote:

Thanks for all your time/help. Well, each time I execute, the whole group of
text cells moves down one row in unison but none of the cells move to their
respective columns or change columns at all. ??

"slarbie" wrote:

Oops, a slight error there - sorry. You'd need one more string variable to
hold the full value of the cell being evaluated evaluating for placement in
row above. Code below edited accordingly.

"slarbie" wrote:

I thought this would be easiest if you inserted a blank row to rearrange the
first record row into the create a new blank row where your just rearranged
row was, and loop through the rows in your recordset. This eliminates the
possibility of overwriting something you didn't intend to.

Assumptions: Header/Title row in row 1; data beginning in row 2 without
"gap" rows. Your "Selection.End(xlUp)" construction seems to assume no gaps
as well. This puts GL in column A, Au in column B, etc... puts anything else
including blank cells in column E, which of course you could rearrange as you
please. See what you think...

Sub MyDataColumnsArranger()

Dim MyVal As String, FullVal As String
Dim r As Integer, c As Integer, i As Integer

Rows(2).Insert shift:=xlDown

r = Range("a3").End(xlDown)
For r = 3 To r
For i = 1 To 4
FullVal = cells(r, i)
MyVal = Left(Cells(r, i), 2)
Select Case MyVal
Case "GL": c = 1
Case "Au": c = 2
Case "WC": c = 3
Case "CA": c = 4
End Select
Cells(r - 1, c).Value = FullVal
Next i
Rows(r).ClearContents
Next r

End Sub


"mypetduke" wrote:

Let me re-ask my question perhaps more intelligently and more fully. My goal
is to scan a spreadsheet with data in many columns but in many cases the data
is in the wrong column. The data all starts with a string which easily
identifies which column it should go into. For example if my columns were
Fruit, Cars, People, the data would be Fruit: apply, or Cars: Chevy, or
People: Men. So I need a code which looks at each cell for a certain word
and place that whole cell's contents/string into the right column (which I
would rather set aside away as an extra column). The ROW should stay the
SAME though since the other rows/columns may be okay? (unless I just write a
code to move it all (which would be nice). Hope that was clear. here's what
I have that doesn't work. Any help would be very greatly appreciated as I
continue to read and learn early in this new "career."

Sub Macro1()

Set myrange = ActiveSheet.Range(Selection, Selection.End(xlUp))
For Each cell In myrange
If Left(cell.Value, 5) = "GL Op" Then
cell.Cut
cell.Paste ("GL Op")
ElseIf Left(cell.Value, 5) = "Au Op" Then
cell.Cut
cell.Paste ("Au Op")
ElseIf Left(cell.Value, 5) = "WC Op" Then
cell.Cut
cell.Paste ("WC Op")
ElseIf Left(cell.Value, 5) = "CA Op" Then
cell.Cut
cell.Paste ("Au Op")
End If
Next cell

End Sub

This ran fine on my machine with Excel 2007, and there's nothing in it that
wouldn't work for 2003 version. The error message suggests you may just have
too many rows to dim integers. You could try dimming as long instead. Many
people never use the integer data type, but go directly to long without
passing go.

so:
dim c as long, i as long, r as long

"mypetduke" wrote:

Now it says "run time error 6 overflow" and, of course I have no idea what
that means. But again I do appreciate your help.


"slarbie" wrote:

apologies. was at work and wrote it in a hurry. just add ".row" to the one
line that finds the last row so it looks like this:

r = Range("a3").End(xlDown).row

and of course change the column numbers to be where your columns really
should be. Hope that helps.

"mypetduke" wrote:

Thanks for all your time/help. Well, each time I execute, the whole group of
text cells moves down one row in unison but none of the cells move to their
respective columns or change columns at all. ??

"slarbie" wrote:

Oops, a slight error there - sorry. You'd need one more string variable to
hold the full value of the cell being evaluated evaluating for placement in
row above. Code below edited accordingly.

"slarbie" wrote:

I thought this would be easiest if you inserted a blank row to rearrange the
first record row into the create a new blank row where your just rearranged
row was, and loop through the rows in your recordset. This eliminates the
possibility of overwriting something you didn't intend to.

Assumptions: Header/Title row in row 1; data beginning in row 2 without
"gap" rows. Your "Selection.End(xlUp)" construction seems to assume no gaps
as well. This puts GL in column A, Au in column B, etc... puts anything else
including blank cells in column E, which of course you could rearrange as you
please. See what you think...

Sub MyDataColumnsArranger()

Dim MyVal As String, FullVal As String
Dim r As Integer, c As Integer, i As Integer

Rows(2).Insert shift:=xlDown

r = Range("a3").End(xlDown)
For r = 3 To r
For i = 1 To 4
FullVal = cells(r, i)
MyVal = Left(Cells(r, i), 2)
Select Case MyVal
Case "GL": c = 1
Case "Au": c = 2
Case "WC": c = 3
Case "CA": c = 4
End Select
Cells(r - 1, c).Value = FullVal
Next i
Rows(r).ClearContents
Next r

End Sub


"mypetduke" wrote:

Let me re-ask my question perhaps more intelligently and more fully. My goal
is to scan a spreadsheet with data in many columns but in many cases the data
is in the wrong column. The data all starts with a string which easily
identifies which column it should go into. For example if my columns were
Fruit, Cars, People, the data would be Fruit: apply, or Cars: Chevy, or
People: Men. So I need a code which looks at each cell for a certain word
and place that whole cell's contents/string into the right column (which I
would rather set aside away as an extra column). The ROW should stay the
SAME though since the other rows/columns may be okay? (unless I just write a
code to move it all (which would be nice). Hope that was clear. here's what
I have that doesn't work. Any help would be very greatly appreciated as I
continue to read and learn early in this new "career."

Sub Macro1()

Set myrange = ActiveSheet.Range(Selection, Selection.End(xlUp))
For Each cell In myrange
If Left(cell.Value, 5) = "GL Op" Then
cell.Cut
cell.Paste ("GL Op")
ElseIf Left(cell.Value, 5) = "Au Op" Then
cell.Cut
cell.Paste ("Au Op")
ElseIf Left(cell.Value, 5) = "WC Op" Then
cell.Cut
cell.Paste ("WC Op")
ElseIf Left(cell.Value, 5) = "CA Op" Then
cell.Cut
cell.Paste ("Au Op")
End If
Next cell

End Sub