I have the following code to find and replace a values within a range that
works fine most of the time but under some situtations which I have not
determined it only seems to look at the current cell. If I select the range
first it does work.
I have set the breakpoint prior to this code being called and done a find
through the interface with out selecting a range. It doesn't the find the
value as it only seems to be searching the current cell. In order to get
find in the interface working to search the sheet again again I have to
select a range first.
Do I need to set some other parameter or clear something to make sure it is
using my range not the current cell or is my only option to include selection
of the range first in the code?


Public Function replaceStringInRange(r As range, findstring As String,
replacewith As String) As Integer
' finds cells within range replaces string

Dim firstaddress As String
Dim c As Range
Dim count As Integer



count = 0
With r
Set c = .Find(findstring, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
firstaddress = c.Address
Do
c.Value = replacewith
count = count + 1
Set c = .FindNext(c)
If c Is Nothing Then
Exit Do
End If
If c.Address = firstaddress Then
Exit Do
End If
Loop
End If
End With

replaceStringInRange = count

End Function

I've never been a fan of not specifying all the parms to the .find statement.

..Find(findstring, LookIn:=xlValues, LookAt:=xlWhole)

I'd use:
..Find(what:=findstring, _
after:=.Cells(.cells.count), _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlnext, _
MatchCase:=False)

If you don't specify all the parms, then excel will use whatever was used
last--that includes by your code, someone else's code or even the user.

(Maybe it's the matchcase parm????)

What are you looking for? Sometimes dates can be difficult to get to work
correctly, too.

Emily wrote:

I have the following code to find and replace a values within a range that
works fine most of the time but under some situtations which I have not
determined it only seems to look at the current cell. If I select the range
first it does work.
I have set the breakpoint prior to this code being called and done a find
through the interface with out selecting a range. It doesn't the find the
value as it only seems to be searching the current cell. In order to get
find in the interface working to search the sheet again again I have to
select a range first.
Do I need to set some other parameter or clear something to make sure it is
using my range not the current cell or is my only option to include selection
of the range first in the code?

Public Function replaceStringInRange(r As range, findstring As String,
replacewith As String) As Integer
' finds cells within range replaces string

Dim firstaddress As String
Dim c As Range
Dim count As Integer



count = 0
With r
Set c = .Find(findstring, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
firstaddress = c.Address
Do
c.Value = replacewith
count = count + 1
Set c = .FindNext(c)
If c Is Nothing Then
Exit Do
End If
If c.Address = firstaddress Then
Exit Do
End If
Loop
End If
End With

replaceStringInRange = count

End Function

--

Dave Peterson

thanks Dave. that seems to work. I am just searching for exact match on a
plain string. I will follow your advise when using the .find statement

"Dave Peterson" wrote:

I've never been a fan of not specifying all the parms to the .find statement.

..Find(findstring, LookIn:=xlValues, LookAt:=xlWhole)

I'd use:
..Find(what:=findstring, _
after:=.Cells(.cells.count), _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlnext, _
MatchCase:=False)

If you don't specify all the parms, then excel will use whatever was used
last--that includes by your code, someone else's code or even the user.

(Maybe it's the matchcase parm????)

What are you looking for? Sometimes dates can be difficult to get to work
correctly, too.

Emily wrote:

I have the following code to find and replace a values within a range that
works fine most of the time but under some situtations which I have not
determined it only seems to look at the current cell. If I select the range
first it does work.
I have set the breakpoint prior to this code being called and done a find
through the interface with out selecting a range. It doesn't the find the
value as it only seems to be searching the current cell. In order to get
find in the interface working to search the sheet again again I have to
select a range first.
Do I need to set some other parameter or clear something to make sure it is
using my range not the current cell or is my only option to include selection
of the range first in the code?

Public Function replaceStringInRange(r As range, findstring As String,
replacewith As String) As Integer
' finds cells within range replaces string

Dim firstaddress As String
Dim c As Range
Dim count As Integer



count = 0
With r
Set c = .Find(findstring, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
firstaddress = c.Address
Do
c.Value = replacewith
count = count + 1
Set c = .FindNext(c)
If c Is Nothing Then
Exit Do
End If
If c.Address = firstaddress Then
Exit Do
End If
Loop
End If
End With

replaceStringInRange = count

End Function

--

Dave Peterson