VBA Find and replace

[Legacy 93538]

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?

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

 

[Ralajer]

You might be able to overcome the partial match if you reverse the direction of the loop thus checking the G46 before G4. Not sure why the partial match doesn't show up in all cases.

For Ndx1 = UBound(RefArray) To 1 Step -1

And to be sure that I understand what you are doing.
Pre-macro
[D2] =M2+G2+11
[L2] =-1
Post-Macro
[D2] = -1+G2+11

If the above is correct you may want to try this method it should be a little faster, fewer loops.

Sub ReplaceColumReference()
    Dim lastFormulaRow As Long
    Dim iRow As Long
    Dim valReplace As Variant
    Dim refType As Variant
    Dim OldCol As String
    Dim NewCol As String
    
    OldCol = "M"
    NewCol = "L"
    Application.ScreenUpdating = False
    Application.Calculate = xlCalculationManual
    lastFormulaRow = Cells(Rows.Count, OldCol).End(xlUp).Row
    '// Loop from last to first to avoid replacing on partial match
    For iRow = lastFormulaRow To 2 Step -1
        
        valReplace = Cells(iRow, NewCol).Value
        
        '// Special case handling adjust if needed
        If Len(valReplace) = 0 Then
            valReplace = 0
        ElseIf Not IsNumeric(valReplace) Then
            valReplace = """" & valReplace & """"
        End If
        '// Loop through various reference types [$A$1], [A$1], [$A1], [A1]
        For Each refType In Array("$" & OldCol & "$" & iRow, _
                                        OldCol & "$" & iRow, _
                                        "$" & OldCol & iRow, _
                                        OldCol & iRow)
            Columns("D:D").Replace What:=refType, Replacement:=valReplace, _
                    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                    SearchFormat:=False, ReplaceFormat:=False
        Next refType
    Next iRow
    Application.Calculate = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
End Sub

 

[Legacy 93538]

Hi

Thanks for replying!

Yeah that is how it should work.

I tried your code and i got an error "Expected Function or Variable" when it got to this line:

Application.Calculate = xlCalculationManual

 

[Ralajer]

Sorry about that I added that at the last minute and didn't test it was meant to be Calculation not Calculate.

Application.[COLOR="Red"]Calculation [/COLOR]= xlCalculationManual
'\\and here too.
Application.[COLOR="Red"]Calculation [/COLOR]= xlCalculationAutomatic

 

[Legacy 93538]

Hi

I tried your code and it only replaced a few of the cell references and not all of them. I have pasted a screen shot in to give an example of what the code you gave me produced.


-- removed inline image ---

 

[Ralajer]

I am not seeing the image on my end could you repost it or provide the URL.