trying to find #REF! in a formula to replace with a sheet name

[funkymonkUK]

Hi have the following piece of code


Sub test()
For Each ws In Worksheets

Set e = .Find(what:="#REF!", LookIn:=xlFormulas)
If Not e Is Nothing Then
firstAddress = e.Address
Do
e.Replace what:="#REF!", Replacement:="'Case-by-cas
mgmt'!", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'ActiveCell.Offset(0, 2).Value = "Received"
'ActiveCell.Offset(0, 18).Value = e.Value
Set e = .FindNext(e)
Loop While Not e Is Nothing And e.Address <> firstAddress
End If

Next
End Sub


when i run this it breaks at the set e = .find part and highlights th
.find the error message is Compile Error: Invalid or unqualifie
reference.

any ideas

 

[Bob Umlas]

you need ws.Find, not just .Find.
..Find implies you're inside a With/End With block, and that's just not the
case.

"funkymonkUK" <[email protected]>
wrote in message
news:[email protected]...

 

[funkymonkUK]

Thanks Bob

I thought that might of been the case as I had copied from anothe
project of mine which had With Statement

However I replaced that get another error

I have
Sub test()
For Each ws In Worksheets

Set e = ws.Find(what:="#REF!", LookIn:=xlFormulas)
If Not e Is Nothing Then
firstAddress = e.Address
Do
e.Replace what:="#REF!", Replacement:="'Case-by-case mgmt'!", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'ActiveCell.Offset(0, 2).Value = "Received"
'ActiveCell.Offset(0, 18).Value = e.Value
Set e = ws.FindNext(e)
Loop While Not e Is Nothing And e.Address <> firstAddress
End If

Next
End Sub


I am now getting Run-tim 438: Object doesn't support this property o
method. I think because ws is a sheet and I dont think a sheet has go
a find feature. how do I get it to search each cells formula in a shee
then move on to the next sheet.

A big thank you for your respons

 

[Dave Peterson]

Since you're cleaning up the #ref! errors, you don't need to keep track of the
first address. After each of the cells with the errors is fixed, then that cell
will never be found again.

I'd do something like:

Option Explicit
Sub test()
Dim ws As Worksheet
Dim e As Range

For Each ws In Worksheets
Do
Set e = ws.Cells.Find(what:="#REF!", LookIn:=xlFormulas)
If e Is Nothing Then
Exit Do
End If
e.Replace what:="#REF!", Replacement:="'Case-by-case mgmt'!", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'e.Offset(0, 2).Value = "Received"
'e.Offset(0, 18).Value = e.Value
Loop
Next ws
End Sub

And if you're not using the e.offset() stuff, then you could just edit|replace
all instead of searching through each formula (but I bet you want that
e.offset() stuff!)

And I would add all the parms in the .find() statement. Excel and VBA will
remember the parms that were used the previous time (manual or through code).
And you might not get what you want if someone used xlwhole in the previous
..find.

 

[funkymonkUK]

no i dont need the e.offset stuff

so does that mean i dont have to use the find i should instead use just
the replace code?

 

[Dave Peterson]

Yep.

Option Explicit
Sub testme()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.Cells.Replace What:="#ref!", _
Replacement:="'Case-by-case mgmt'!", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
Next wks
End Sub