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
wrote:
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