Replace: Object Required

[Guest]

Here' my code:
Dim varSht As Variant
Dim FoundCell As Range

' Iterates through sheets, finds data, & replaces data
For Each varSht In Workbooks(sFilename).Sheets
' Use Excel's Internal Find & Replace Method
Set FoundCell = varSht.Cells.Replace(What:=sFindWhat, _
Replacement:=sReplaceWith, _
LookAt:=vLookAt, MatchCase:=bMatchCase)
If Not FoundCell Is Nothing Then
fncStdFind = True
Exit Function
End If
Next

When I run this, the 'Set FoundCell = varSht.Cells.Replace' line is
highlighted, and the error 'Object required.' is displayed.
If I change 'Dim varSht As Variant' to 'Dim varSht As WorkSheet', then it
gives me a 'Type mismatch' error with the same line highlighted.
What the heck?

 

[Guest]

What are vLookAt and bMatchCase at runtime. As a guess that is where your
problem lies. vLookAt Should be text such as xlWhole or xlPart. bMatchCase
will be boolean.

 

[Guest]

Hi Jim,
vLookAt & bMatchCase are user defined variables passed into the procedure.
I replaced them with xlPart & False and still got the same error. The
interesting thing is that using Find method in the same manner does *not*
return this 'Object required' error, but runs fine.
Any othe suggestions?
Thanks for your help.
Trent Argante
[DC.J(455)]

 

[Rick Hansen]

Good Morning Trent,
I believe it is excel is causing your problem. I ran several test a got
the same results as you. When read in VBA help the Replace method replaces
all occureance in the selected range. In your case you have selected the
complete sheet. So for ever matching cell found, excel would have return
range object. This would be very impractical. Because excel be passing
multiple range object. Thus probably causing the error. I've attached some
work around code that will probably help you get your task done....

Enjoy, Rick, Fairbanks Alaska...

Dim varSht As Worksheet
Dim FoundCell As Range
Dim FirstAdd As String

' Iterates through sheets, finds data, & replaces data
For Each varSht In Worksheets
' Use Excel's Internal Find & Replace Method
Set FoundCell = varSht.Cells.Find(What:=sFindWhat, _
LookAt:=xlPart, MatchCase:=True)
If Not FoundCell Is Nothing Then
fncStdFind = True
FirstAdd = FoundCell.Address
Do
FoundCell.Value = sReplaceWith
Set FoundCell = Cells.FindNext(FoundCell)
Loop While (FoundCell.Address <> FirstAdd)
Exit Function
Else
fncStdFind = False
End If
Next

Hi Jim,
vLookAt & bMatchCase are user defined variables passed into the procedure.
I replaced them with xlPart & False and still got the same error. The
interesting thing is that using Find method in the same manner does *not*
return this 'Object required' error, but runs fine.
Any othe suggestions?
Thanks for your help.
Trent Argante
[DC.J(455)]

What are vLookAt and bMatchCase at runtime. As a guess that is where your
problem lies. vLookAt Should be text such as xlWhole or xlPart. bMatchCase
will be boolean.

 

[Guest]

Thanks rick, that did the trick!
Incidentally, my best high school buddy is from Fairbanks, but that was
almost Ahhhh 30 years ago!!! =;O)
--
Trent Argante
[DC.J(455)]

Good Morning Trent,
I believe it is excel is causing your problem. I ran several test a got
the same results as you. When read in VBA help the Replace method replaces
all occureance in the selected range. In your case you have selected the
complete sheet. So for ever matching cell found, excel would have return
range object. This would be very impractical. Because excel be passing
multiple range object. Thus probably causing the error. I've attached some
work around code that will probably help you get your task done....

Enjoy, Rick, Fairbanks Alaska...

Dim varSht As Worksheet
Dim FoundCell As Range
Dim FirstAdd As String

' Iterates through sheets, finds data, & replaces data
For Each varSht In Worksheets
' Use Excel's Internal Find & Replace Method
Set FoundCell = varSht.Cells.Find(What:=sFindWhat, _
LookAt:=xlPart, MatchCase:=True)
If Not FoundCell Is Nothing Then
fncStdFind = True
FirstAdd = FoundCell.Address
Do
FoundCell.Value = sReplaceWith
Set FoundCell = Cells.FindNext(FoundCell)
Loop While (FoundCell.Address <> FirstAdd)
Exit Function
Else
fncStdFind = False
End If
Next

Hi Jim,
vLookAt & bMatchCase are user defined variables passed into the procedure.
I replaced them with xlPart & False and still got the same error. The
interesting thing is that using Find method in the same manner does *not*
return this 'Object required' error, but runs fine.
Any othe suggestions?
Thanks for your help.
Trent Argante
[DC.J(455)]

What are vLookAt and bMatchCase at runtime. As a guess that is where your
problem lies. vLookAt Should be text such as xlWhole or xlPart. bMatchCase
will be boolean.
--
HTH...

Jim Thomlinson


:

Here' my code:
Dim varSht As Variant
Dim FoundCell As Range

' Iterates through sheets, finds data, & replaces data
For Each varSht In Workbooks(sFilename).Sheets
' Use Excel's Internal Find & Replace Method
Set FoundCell = varSht.Cells.Replace(What:=sFindWhat, _
Replacement:=sReplaceWith, _
LookAt:=vLookAt, MatchCase:=bMatchCase)
If Not FoundCell Is Nothing Then
fncStdFind = True
Exit Function
End If
Next

When I run this, the 'Set FoundCell = varSht.Cells.Replace' line is
highlighted, and the error 'Object required.' is displayed.
If I change 'Dim varSht As Variant' to 'Dim varSht As WorkSheet', then it
gives me a 'Type mismatch' error with the same line highlighted.
What the heck?