ExcelBanter - View Single Post - trying to find #REF! in a formula to replace with a sheet name

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