Any ideas... Doesn't work...

[Andrew Slentz]

I can't see what I have wrong on this macro... If I have the following
sample data:

Column C: 1234 Towne Lake Pkwy., Bldg. 222
Column D: Area 12, Suite 123, Woodstock
Column E: GA

I need anything to the left of the last comma in column D to be appended
to the end of the info. in column C. This doesn't seem to be working
right. Any ideas???

Thanks,

Andrew

Here's the macro!

Sub Address()

Dim i As Long, lastrow As Long
Dim j As Long, k As Long, iloc As Long
Dim sStr As String
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lastrow Step 1
sStr = Cells(i, 4).Value
j = Len(sStr)
iloc = 0
For k = j To 1 Step -1
If Mid(sStr, k, 1) = "," Then
iloc = k
Exit For
End If
Next
If iloc <> 0 Then
Cells(i, 3).Value = Cells(i, 4). _
Value & " " & _
Trim(Left(sStr, iloc - 1))
Cells(i, 4).Value = Trim( _
Right(sStr, j - iloc))
End If
Next
End Sub

 

[Don Guillett]

try using instrrev to find the last comma.
InStrRev Function


Description

Returns the position of an occurrence of one string within another, from the
end of string.

Syntax

InstrRev(stringcheck, stringmatch[, start[, compare]])