VBA "Replace" not working as expected

[WesJD]

I'm using the "replace" function to revise a chart formula (to shift the Name, X, and Y ranges a specified number of columns to the right). The replace works correctly the first time through the loop, but the second time it misses an occurrence of the search string. It replaces the text at positions 45 to 47, missing an earlier occurrence at positions 39 to 41 even though the instr function found it. What am I missing?

The relevant snippet of my code is here:

    '   Find and replace all the column letters used in the current chart formula
    Dim intStartPos As Integer
    Dim str1 As String
    Dim str2 As String
    intStartPos = 1
    For Each s In cht.SeriesCollection
        Debug.Print s.Formula
        Do
            '   Find each column letter
            intDollar1 = InStr(intStartPos, s.Formula, "$", vbTextCompare)
            If intDollar1 > 0 Then
                '   Get the column letter
                intDollar2 = InStr(intDollar1 + 1, s.Formula, "$")
                Debug.Print "Found dollar signs in positions " & intDollar1 & " and " & intDollar2
                strCol1 = Mid(s.Formula, intDollar1 + 1, intDollar2 - intDollar1 - 1)
                str1 = "$" & strCol1 & "$"
                str2 = "$" & GetColumnLetter(GetColumnNumber(str1) + intOffset) & "$"
                '   Replace with the letter of the column with the specified offset
                Debug.Print "Replacing " & str1 & " with " & str2
                s.Formula = Replace(s.Formula, str1, str2, , 1, vbTextCompare)
                Debug.Print s.Formula
                intStartPos = intDollar2 + 1
            End If
        Loop Until intDollar1 = 0
    Next

The debug output is here, using an offset of 16 which should change column D to column T

=SERIES(Calculation!$D$10,Calculation!$D$12:$D$44,Calculation!$E$12:$E$44,1)
Found dollar signs in positions 21 and 23
Replacing $D$ with $T$
=SERIES(Calculation!$T$10,Calculation!$D$12:$D$44,Calculation!$E$12:$E$44,1)
Found dollar signs in positions 39 and 41
Replacing $D$ with $T$
=SERIES(Calculation!$T$10,Calculation!$D$12:$T$44,Calculation!$E$12:$E$44,1)
Found dollar signs in positions 45 and 47

 

[RoryA]

Is there a reason you are choosing to only make 1 replacement each time, rather than just replacing all instances?

 

[WesJD]

Thanks for your interest! If I replace all instances, then I don't have a way of knowing whether the next column letter returned by my search has already been replaced or not.

 

[RoryA]

Personally I'd probably build up a unique set of column refs and replacements then loop. I'd assume the cause of what you're seeing is that when you assign $T$12:$D$44 as the range in the formula, Excel converts that to the more logical $D$12:$T$44. You'd be better off putting the formula into a string, work with that, then put the string back as the formula.

 

[WesJD]

Ah, that's the explanation! Thanks very much, working with a string solved my problem.