Search and Replace part of a formula

**onemoremile** · 11-22-2013, 12:29 PM

Hi,

I have a formula in my worksheet range G2:G325. It's similar to this:

=GETPIVOTDATA("[Measures].[KPI A]",'Data Cube'!$A$3,"[Operator].[Operator]","[Operator].[Operator].[Operator].&[XXX]","[Date].[Date]","[Date].[Date].[Week].&[2013]&[11]&[1]")

Where [XXX] is a unique 3 or 4 digit identifier

Where [2013]&[11]&[1]") is equivalent to a week range

I would like to find [2013]&[11]&[1]") or any week range in this formula and replace it with the items I've created in my listbox (i.e.:[2013]&[11]&[2]"), [2013]&[11]&[3]"), etc)

Is there a way to do this?

I've recorded macros to do it manually, but since the range is fairly large (and growing) I was wondering if there is a way
to apply the change to the specified range.

Edited to add code attempt:

Range("AD12").Select
    ActiveCell.FormulaR1C1 = "[2013]&[11]&[1]"")"
    Range("G3").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""[Measures].[KPI A]"",'Data Cube'!R3C1,""[Operator].[Operator]"",""[Operator].[Operator].[Operator].&[712]"",""[  Date].[  Date]"",""[  Date].[  Date].[Week].&[2013]&[11]&[1]"")"
    Range("G4").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""[Measures].[KPI A]"",'Data Cube'!R3C1,""[Operator].[Operator]"",""[Operator].[Operator].[Operator].&[805]"",""[  Date].[  Date]"",""[  Date].[  Date].[Week].&[2013]&[11]&[1]"")"
    Range("G5").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""[Measures].[KPI A]"",'Data Cube'!R3C1,""[Operator].[Operator]"",""[Operator].[Operator].[Operator].&[1214]"",""[  Date].[  Date]"",""[  Date].[  Date].[Week].&[2013]&[11]&[1]"")"
    Range("G6").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""[Measures].[KPI A]"",'Data Cube'!R3C1,""[Operator].[Operator]"",""[Operator].[Operator].[Operator].&[883]"",""[  Date].[  Date]"",""[  Date].[  Date].[Week].&[2013]&[11]&[1]"")"
    Range("G7").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""[Measures].[KPI A]"",'Data Cube'!R3C1,""[Operator].[Operator]"",""[Operator].[Operator].[Operator].&[2348]"",""[  Date].[  Date]"",""[  Date].[  Date].[Week].&[2013]&[11]&[1]"")"
    Range("G8").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""[Measures].[KPI A]"",'Data Cube'!R3C1,""[Operator].[Operator]"",""[Operator].[Operator].[Operator].&[1673]"",""[  Date].[  Date]"",""[  Date].[  Date].[Week].&[2013]&[11]&[1]"")"
    Range("G9").Select
    ActiveCell.FormulaR1C1 = _

Any help would be appreciated.

**onemoremile** · 11-22-2013, 02:16 PM

Did some research and came across this:

Sub Find_Replace()
For Each Cell In Range("C1:C" & Cells(Rows.Count, 3).End(xlUp).Row)
    Cell.Formula = Left(Cell.Formula, InStr(1, Cell.Formula, """")) & Cell.Offset(0, -1).Value & Right(Cell.Formula, Len(Cell.Formula) - InStr(1, Cell.Formula, """"))
Next
End Sub

I know I would need to change the cell range (my data is in G2:G325) but the offset is confusing. I have the actual
list of strings that I need inserted into the formula. The first is [2013]&[11]&[3]"), the next week would be [2013]&[11]&[4]"), and so on. But I don't think I need the offset functionality?

Anyone have input?

Thanks