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?
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