bassman71
09-25-2006, 07:07 PM
I have the following that reverses what's on either side of a " " , but leaves everything else alone. The reversing part works OK but I cannot figure out how to piece it back together using the & operator. Explained in comment.
Sub Macro1()
'cell shows "Morales Contido, Louis"
'and I'm trying to get "Contido Morales, Louis"
'the code is almost there, I can reverse the names OK,
'however when I add the '& Remain' to the code after the Rev1 Rev2, it populates the cell "Louis"
Dim i As Integer, j As Integer, k As Integer
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = LastRow To 1 Step -1
With Cells(i, 1)
j = InStr(.Value, " ") 'equals 8
k = InStr(.Value, ",") 'equals 16
Rev1 = Mid(.Value, j + 1, (k - 1) - j)
Rev2 = Mid(.Value, 1, j - 1)
Remain = Mid(.Value, k + 1) 'this one is dogging me
Cells(i, 2).Value = Rev1 & " " & Rev2 & "," & Remain
' if I leave off the &
'Remain, it shows up "Contido Morales," but after adding
'& Remain, cell shows up as "Louis"
End With
Next
End Sub
Thanks .............:banghead:
Sub Macro1()
'cell shows "Morales Contido, Louis"
'and I'm trying to get "Contido Morales, Louis"
'the code is almost there, I can reverse the names OK,
'however when I add the '& Remain' to the code after the Rev1 Rev2, it populates the cell "Louis"
Dim i As Integer, j As Integer, k As Integer
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = LastRow To 1 Step -1
With Cells(i, 1)
j = InStr(.Value, " ") 'equals 8
k = InStr(.Value, ",") 'equals 16
Rev1 = Mid(.Value, j + 1, (k - 1) - j)
Rev2 = Mid(.Value, 1, j - 1)
Remain = Mid(.Value, k + 1) 'this one is dogging me
Cells(i, 2).Value = Rev1 & " " & Rev2 & "," & Remain
' if I leave off the &
'Remain, it shows up "Contido Morales," but after adding
'& Remain, cell shows up as "Louis"
End With
Next
End Sub
Thanks .............:banghead: