Sleeper: Code issues

[Emjay]

I have the following code that looks good but doesn't work:


Range("K2").Select
Do While IsEmpty(ActiveCell.Offset(0, -2).Value) = False
If InStr((Len(ActiveCell.Value) / 2), ActiveCell.Value, " ST") = Len(ActiveCell.Value) - 3 Then
ActiveCell.Replace What:=" ST", Replacement:=" STREET", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If
ActiveCell.Offset(1, 0).Range("A1").Select
Loop

Column I has the peoples last names.
Column K has their addresses, for example "21 STATE ST"
The code should change ST to STREET but leave STATE as is.
However it does nothing to the address.

Does anyone have any ideas?

[nasomi]

Sounds like you're overcomplicating it. Here's what I would do.


columns("K").replace what:=" ST", replacement:=" STREET"

Less lines means easier to trouble-shoot.

[Emjay]

Sounds like you're overcomplicating it. Here's what I would do.

columns("K").replace what:=" ST", replacement:=" STREET"

Less lines means easier to trouble-shoot.

Agreed, simpler is better.
However, your code would change STATE to STREETATE and STREET to STREETREET, etc. Which isn't acceptable.
That's why I had to make it more complicated.

[nasomi]

Ooh, I did not account for that.


range("K2").select
'assume k1 has header value
for a = 1 to activesheet.usedrange.rows.count
if right(activecell,3).value = " ST" then
activecell = left(activecell,(len(activecell)-3)) & " STREET"
end if
activecell.offset(1,0).select
next a

Might that work?

[Kenneth Hobs]

If St could occur before the end of a string, then checking the last word in the string would not always solve your problem.

In this method, only single space characters would be returned with some formatting stripped sometimes.


Sub FR()
Dim r As Range, s() As String, i As Integer
Set r = Range("K2")
Do While IsEmpty(r.Offset(0, -2).Value) = False
s() = Split(r)
For i = 0 To UBound(s)
If s(i) = "St" Then
s(i) = "Street"
r.Value = Join(s)
GoTo NextLoop
End If
Next i
NextLoop:
Set r = r.Offset(1, 0)
Loop
End Sub

[Emjay]

Ooh, I did not account for that.


range("K2").select
'assume k1 has header value
for a = 1 to activesheet.usedrange.rows.count
if right(activecell,3).value = " ST" then
activecell = left(activecell,(len(activecell)-3)) & " STREET"
end if
activecell.offset(1,0).select
next a

Might that work?

Good idea. I tried the following and it worked:


Range("K2").Select
Do While IsEmpty(ActiveCell.Offset(0, -2).Value) = False
If Right(ActiveCell.Value, 3) = " ST" Then
ActiveCell.Value = Left(ActiveCell.Value, (Len(ActiveCell.Value) - 3)) & " STREET"
End If
ActiveCell.Offset(1, 0).Range("A1").Select
Loop

[Emjay]

If St could occur before the end of a string, then checking the last word in the string would not always solve your problem.

In this method, only single space characters would be returned with some formatting stripped sometimes.


Sub FR()
Dim r As Range, s() As String, i As Integer
Set r = Range("K2")
Do While IsEmpty(r.Offset(0, -2).Value) = False
s() = Split(r)
For i = 0 To UBound(s)
If s(i) = "St" Then
s(i) = "Street"
r.Value = Join(s)
GoTo NextLoop
End If
Next i
NextLoop:
Set r = r.Offset(1, 0)
Loop
End Sub


I have other code that handled " ST" in the midddle of the string, but this does it all and is nice and clean.
However, this code changes "53 ST JOHN ST" to "53 STREET JOHN ST" which misses the whole point of changing the latter occurence of " ST" only.

[Kenneth Hobs]

You may need to consider the case of "St." as well. For just the end " St" consideration:

Sub FR2()
Dim r As Range
Set r = Range("K2")
Do While IsEmpty(r.Offset(0, -2).Value) = False
If UCase(Right(r.Value, 3)) = " ST" Then r.Value = Left(r.Value, Len(r.Value) - 2) & "STREET"
Set r = r.Offset(1, 0)
Loop
End Sub