ExcelBanter - Find/Replace Specific Range Woes...

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Find/Replace Specific Range Woes... (https://www.excelbanter.com/excel-programming/418347-find-replace-specific-range-woes.html)

Find/Replace Specific Range Woes...

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

Find/Replace Specific Range Woes...

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

Benjamin

On Oct 10, 10:56*am, Dave Peterson wrote:
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