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

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-case 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 the .find the error message is Compile Error: Invalid or unqualified reference.

any ideas?

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]...
>
> 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-case
> 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 the
> find the error message is Compile Error: Invalid or unqualified
> reference.
>
> any ideas?
>
>
> --
> funkymonkUK
> ------------------------------------------------------------------------
> funkymonkUK's Profile:
http://www.excelforum.com/member.php...o&userid=18135
> View this thread: http://www.excelforum.com/showthread...hreadid=556506
>

Thanks Bob

I thought that might of been the case as I had copied from another 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 or method. I think because ws is a sheet and I dont think a sheet has got a find feature. how do I get it to search each cells formula in a sheet then move on to the next sheet.

A big thank you for your response

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 wrote:
>
> Thanks Bob
>
> I thought that might of been the case as I had copied from another
> 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 or
> method. I think because ws is a sheet and I dont think a sheet has got
> a find feature. how do I get it to search each cells formula in a sheet
> then move on to the next sheet.
>
> A big thank you for your response
>
> --
> funkymonkUK
> ------------------------------------------------------------------------
> funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
> View this thread: http://www.excelforum.com/showthread...hreadid=556506

--

Dave Peterson

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?

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



funkymonkUK wrote:
>
> 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?
>
> --
> funkymonkUK
> ------------------------------------------------------------------------
> funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
> View this thread: http://www.excelforum.com/showthread...hreadid=556506

--

Dave Peterson