Is there a way to Find & Replace a cell reference value in Excel 2010? VBA?

I have an Excel worksheet that contains a bunch of charts created from data pulled from another worksheet in the workbook. At the beginning of each month, when I add last month's data to the data worksheet, I shift everything over one column such that what was last month 开发者_StackOverflowis now in column O, and what is now last month is always in column P. Problem is, I want the worksheet with the charts to always chart the current "last month" (always column P), but it's "smart" enough to re-configure the charts to keep charting the data it had been charting (i.e., the previous "last month"), which is now in column O.

Specifically, I'd like to change (for example)

=SERIES("Visits",Data!$D$4:$O$4,Data!$D$4:$O$4,1)

to

=SERIES("Visits",Data!$D$4:$P$4,Data!$D$4:$P$4,1)

Find & Replace doesn't seem capable of doing that. Is it possible with VBA? (I'm a total VBA noob.)

---

I think the problem here is how you're "shifting" the data. If you are deleting/inserting or moving data then Excel will update its references. You could copy the data and paste it 1 column to the left, and then paste your current 'last month' data into column P.

In short, copying and pasting will not force Excel to update its pointers, moving/deleting/inserting will.

If this isn't an option look into the OFFSET function - if you can incorporate that into the data-range you might be able to do what you want.

---

I would suggest using dynamic ranges which automatically extend your range as data is added, see this example from Jon Peltier for a start

---

There is no built-in way to perform find-replace on series formulas, but you can write VBA code to do it. I've written a tutorial about this, Change Series Formula – Improved Routines. I show a number of VBA routines, but the simplest is below.

The following sub checks for an active chart, then prompts the user for 'change what' and 'to what' strings, $O$ and $P$ in your case, and then changes all series formulas in the active chart.

Sub ChangeSeriesFormula()
    ''' Just do active chart
    If ActiveChart Is Nothing Then
        '' There is no active chart
        MsgBox "Please select a chart and try again.", vbExclamation, _
            "No Chart Selected"
        Exit Sub
    End If

    Dim OldString As String, NewString As String, strTemp As String
    Dim mySrs As Series

    OldString = InputBox("Enter the string to be replaced:", "Enter old string")

    If Len(OldString) > 1 Then
        NewString= InputBox("Enter the string to replace " & """" _
            & OldString & """:", "Enter new string")
        '' Loop through all series
        For Each mySrs In ActiveChart.SeriesCollection
            strTemp = WorksheetFunction.Substitute(mySrs.Formula, _
                OldString, NewString)
            mySrs.Formula = strTemp
        Next
    Else
        MsgBox "Nothing to be replaced.", vbInformation, "Nothing Entered"
    End If
End Sub

You can extract the core of this routine and call it from other routines that cycle through all charts on a worksheet, all charts in a workbook, etc.