Find and Replace macro

[Legacy 93538]

Hi

I need some help with a find and replace statement i am trying to create. The code below should loop through the D column (which has values similar to this "'=G4+H4+I4+J4+K4+L4+M4+N4+O4+P4+Q4+R4"), it should then look in the cell value and for each cell ref it finds, it looks that cell ref up in the M column and replaces the cell ref with the value in the corresponing row in column L.

I have been using the following code but when it runs, it doesn't replace any of the cell refs, prehaps beacuse its looking at the value as a whole but i am not sure.

For Y = 1 To Z
    FindRef = Range("M" & 1 + Z)    'First loop with substitute values
    ReplRef = Range("L" & 1 + Z)
    
    Set MyRange = PPCWBSht.Range("D:D")
        MyRange.Replace What:=FindRef, Replacement:=ReplRef, LookAt:=xlPart, SearchOrder:=xlByRows, _
                        MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Next Y

Does anyone know of an easier way of doing this?

Thank you

Jessicaseymour

 

[Legacy 93538]

I found out why the code i pasted in my first post does not work.

In the first loop it goes through the D column and looks for the cell ref in M column and replaces it with a subisitute value. I have found that it finds a cell ref inside a cell ref, for example it finds G4 inside G42.

Does anyone know if there is a way to get it look at the value between the math symbols like +cell ref+ or cell ref/?

I hope that makes sense