Find and Replace in VBA

[Legacy 93538]

HI

This is probably an odd question but its a problem i have been trying to solve for a few days and have i come up with nothing.

I have a workbook, which is created using my macro. The macro places foruma in column D, what i need to do is replace there cell reference's in the forumlas with a value.

SO far i have tried having the macro create a list of the cell references and the values (in column L and M) needed to be replaced with and doing a find a replace however this did not work as it replaced every cell ref it found eve if it was inside the value it had already replaced the cell ref with.

I then tried the same but insead of replacing the cell ref with the value i added a section which replaced the cel ref with a subsitute value (Column I) and then did a second find and replace of the subistitue value with the value. However this again did the same thing as before.

I have added the code i am using to do the find and replace on the D column

Z = WorksheetFunction.CountA(PPCWBSht.Range("L2:L20000"))
 
'Loop to convert Excel refs to InpRefs
For Y = 1 To 2
    For X = 1 To Z
        Application.StatusBar = Y & " of " & 2 & "  :  Replacing cell ref with the CalcID : " & X & " of " & Z
        
        If Y = 1 Then
            FindRef = Range("M" & 1 + X)    'First loop with substitute values
            ReplRef = Range("I" & 1 + X)
        Else
            FindRef = Range("I" & 1 + X)    'Second loop with InpRef values
            ReplRef = Range("L" & 1 + X)
        End If
        
        Set MyRange = PPCWBSht.Range("D:D")
            MyRange.Replace What:=FindRef, Replacement:=ReplRef, LookAt:=xlPart, SearchOrder:=xlByRows, _
                MatchCase:=True, SearchFormat:=True, ReplaceFormat:=True
    Next X
Next Y

I have run out of ideas, Can anyone help me??

Thanks

Jeskit
<!-- / message --><!-- attachments -->