changing a cell reference
 

Hi everyone. I am hoping someone is willing to help. I would like to
to test if a cell containing, a cell reference (eg, f(x) = Sheet2!A1),
grabs a value from a particular sheet (in this case Sheet2). If this
is the case, I want to change the sheet which it pulls a value from
(say Sheet1). My question is how do I (a) test this condition and (b)
edit the formula so it references a different sheet?

Here is my code thus far:

Sub ChangeRef()
Dim ws As Worksheet
Dim ReplaceWith As String
Dim ref As String

ref = Left("=#REF", 5)
ReplaceWith = "=Autofilter"

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
For Each cell In ActiveSheet.UsedRange.Cells
'' If cell formula contains #REF! Then replace with
Autofilter!
cell = WorksheetFunction.Replace(ref, 1, 10, ReplaceWith)
'' End If
Next cell
Next ws
End Sub

Thank you to anyone that can help me.

-- Isaac M


--
I Maycotte
------------------------------------------------------------------------
I Maycotte's Profile: http://www.excelforum.com/member.php...o&userid=35604
View this thread: http://www.excelforum.com/showthread...hreadid=553767