Thread
:
Any ideas... Doesn't work...
View Single Post
#
2
Posted to microsoft.public.excel.programming
Don Guillett[_4_]
external usenet poster
Posts: 2,337
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!
Reply With Quote
Don Guillett[_4_]
View Public Profile
Find all posts by Don Guillett[_4_]