Find/Replace Specific Range Woes...

  • Thread starter Thread starter adimax
  • Start date Start date
A

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
 
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
 
The change C2 and change back worked perfectly Dave. You are a scholar
and a gentleman.

Benjamin
 
Back
Top