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