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

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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

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]])


--
Don Guillett
SalesAid Software

"Andrew Slentz" wrote in message
...
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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!