PDA

View Full Version : Solved: Find and Replace in Range - Compare Codes



flea333
09-01-2010, 11:40 AM
Excel help provides this nice example for FindNext to find and replace a value in a specified range. It works nicely until the very last error gave me an error saying the object isn't defined or something on the line

Loop While Not c Is Nothing And c.Address <> firstAddress
Code:

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End WithSince this didn't work and I found it to be extraneous code, I used the following code to fine/replace:


For i = 1 To SRStat.Rows.Count
If SRStat(i) = findtext Then
SRStat(i) = replacetext
End If
Next i


Any comments?

mbarron
09-01-2010, 12:36 PM
You could use the built in Replace (ctrl+h)
Sub findReplace()
Dim r As Range
Set r = Range("a1:j100")
With r
.Replace what:="a", replacement:="z", lookat:=xlWhole
End With
End Sub

flea333
09-01-2010, 01:48 PM
sweet