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"))
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)
FindRef = Range("I" & 1 + X) 'Second loop with InpRef values
ReplRef = Range("L" & 1 + X)
Set MyRange = PPCWBSht.Range("D:D")
MyRange.Replace What:=FindRef, Replacement:=ReplRef, LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=True, SearchFormat:=True, ReplaceFormat:=True
I have run out of ideas, Can anyone help me??