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-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

--
funkymonkU
-----------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...fo&userid=1813
View this thread: http://www.excelforum.com/showthread.php?threadid=55650

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

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"
wrote in message
...

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

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

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

--
funkymonkU
-----------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...fo&userid=1813
View this thread: http://www.excelforum.com/showthread.php?threadid=55650

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

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

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

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

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

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