Find/Replace Specific Range Woes...

[adimax]

I'm trying to use a piece of code I saw posted here by Dave Peterson
and I'm stuck with it not working. What I'm trying to do is go through
a spreadsheet and locate X data where X is the input from a Userform,
change all of the X to Y, but not change the original X on the
worksheet (where it's stored and referenced). Its looking like:

Sub testme01()

Dim FoundCell As Range
Dim FindWhat As String
Dim WithWhat As String

FindWhat = Range("C2").Value
WithWhat = "Elm"

Do
Set FoundCell =
ActiveSheet.Range("B2:B1000").Find(What:=FindWhat, _
After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart,
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.Value = WithWhat
End If
Loop
End Sub

The problem is the ' ActiveSheet.Range("B2:B1000").Find ' part. If I
use the original ' ActiveSheet.Cells.Find ' it works just fine, but it
changes the value of C2 as well, which I do not want. I thought
specifying the range as B2 to B1000 would work, but that fails and I'm
not sure why.

How can I narrow this Find now to a specific part of the worksheet
instead of the entire thing?

Thanks as always in advance,
Benjamin

 

[Dave Peterson]

If the activecell isn't part of the range to change, you could be seeing that
problem.

I'd try:

Set FoundCell = ActiveSheet.Range("B2:B1000").Find(What:=FindWhat, _
After:=ActiveSheet.Range("B2"), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

But I'm not sure if that's the real problem. Did you really want to look at
just B2:B1000?

If no, can you change the range to look at to just rows 3 to ####?

Another option may be use .findnext() (see vba's help)--or even change C2 and
then change c2 back to what it was originally???

Dim OrigC2Val as variant
OrigC2Val = activesheet.range("c2").value
'code to do all the work
activesheet.range("C2").value = origc2val

 

[adimax]

The change C2 and change back worked perfectly Dave. You are a scholar
and a gentleman.

Benjamin