Hi

I really need some help with this!!

I have the code below which loops though the D column, which is filled with forumlas. What this code does is looks at each of the formulas and finds the cell references within the forumla in the M column and replaces it with the value the correpsonding row in column L.

This code does work however i have noticed for a couple of the formulas it finds a cell reference inside a cell refernce for example it finds G4 inside G46.

Can any help explain why it is doing that for a only a few of the cell refernces?

Code:

Dim tempStr, OperatorPosition As String
Dim RefArray, ArrayElement As Variant
Dim FormulaColumn As Range
Dim Formula As Range
Dim LastRefRow, LastFromulaRow, Ndx1, RefPosition, FoundPosition As Long
Application.StatusBar = "Populating calculations column"
'Code to replace cell value
LastRefRow = Cells(Rows.Count, "M").End(xlUp).Row
LastFromulaRow = Cells(Rows.Count, "D").End(xlUp).Row
'load the references and replacement values into an array
RefArray = Range("L2:M" & LastRefRow).Value
Set FormulaColumn = Range("D2:D" & LastFromulaRow)
'this loop scans a formula looking for each of the references in column M
For Each Formula In FormulaColumn
tempStr = Formula.Text
For Ndx1 = 1 To UBound(RefArray)
'for each reference value in the formula
RefPosition = InStr(tempStr, RefArray(Ndx1, 2))
If RefPosition > 0 Then 'If the reference string is found
For Each ArrayElement In Array("*", "/", "+", "-", ")") 'Then find the position of the operator to the right.
OperatorPosition = InStr(RefPosition, tempStr, ArrayElement)
'If statement to find the position of the operator
If OperatorPosition - RefPosition = Len(RefArray(Ndx1, 2)) _
Or Len(tempStr) - RefPosition = Len(RefArray(Ndx1, 2)) - 1 Then
tempStr = Replace(tempStr, RefArray(Ndx1, 2), RefArray(Ndx1, 1))
'If the string is in the middle then the operator gives the length of the string being looked for
' If its not then exit the For loop as its not needed
Exit For
End If
Next ArrayElement
End If

Thank you

Jeskit