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



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

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