pt.GetPivotData().value question

[winglessbuzzard]

I'm having a problem combining variables in a string and using that string for the formula in a GetPivotData VBA (=GetPivotData() in a cell) formula. I'm trying to keep it out of excel to avoid excel's application recalculation time.


I get an Application-defined or Object-defined error on this line

If IsError(cutPT.GetPivotData(NewFrmla).Value) = True Then
                        c.Value = 0
End If

in this subroutine

Sub RunCut(ByVal cutPT As PivotTable, ByVal cWSN As String)
Dim rPLT As Range, i As Integer
Dim cNM As String, cRNG As Range, c As Range, tws As Worksheet
Dim Frmla As String, NewFrmla As String, RevEndRow As Integer, StatsBegRow As Integer
Dim ws As Worksheet, ordr As Integer



Set rPLT = ThisWorkbook.Worksheets("CutSpammer").Range("L3")
Set tws = ThisWorkbook.Worksheets("CutTemplate")

Frmla = tws.Range("I1").Formula


RevEndRow = 23
StatsBegRow = 415

i = 0
Do While Len(rPLT.Offset(i, 0).Formula) ] 0
    cNM = rPLT.Offset(i, 1).Value
    Set cRNG = tws.Range(rPLT.Offset(i, 2).Value)
   
    For Each c In cRNG
        If Len(tws.Range("J" & c.Row).Formula) ] 2 Then
            If c.Offset(-c.Row + 1, 0).Value <> "" And c.Offset(-c.Row + 2, 0).Value <> "" Then
                    NewFrmla = Replace(Frmla, ",1,", "," & Month(c.Offset(-c.Row + 2, 0).Value) & ",")
                    NewFrmla = Replace(NewFrmla, "400000_Food", tws.Range("K" & c.Row).Value)
                    NewFrmla = Replace(NewFrmla, "Material Costs - Standard Material Costs", tws.Range("G" & c.Row).Value)
                    NewFrmla = Replace(NewFrmla, " BGT_2012", cNM)
                    If IsError(cutPT.GetPivotData(NewFrmla).Value) = True Then
                        c.Value = 0
                    Else
                        If c.Row [ RevEndRow Or c.Row ] StatsBegRow Then
                            c.Value = cutPT.GetPivotData(NewFrmla).Value
                        Else
                            c.Value = -1 * cutPT.GetPivotData(NewFrmla).Value
                        End If
                    End If
            End If
        End If
    Next c
    i = i + 1
Loop

End Sub

The exact string in the cell tws.Range("I1").Formula is " BGT_2012", "MN", "1", "T5", "Material Costs - Standard Material Costs", "PL_Metric", "400000_Food"

If I paste this string in place of NewFrmla in the line IsError(cutPT.GetPivotData(NewFrmla).Value), then it works fine.

If I paste the string created by the code up to the above line into the line above, it works fine. Is VBA dropping the quotes off somewhere?

 

[winglessbuzzard]

formula in a GetPivotData VBA (=GetPivotData() in a cell) formula.

should be:

formula in a GetPivotData VBA (NOT =GetPivotData() in a cell) formula.

 

[winglessbuzzard]

I parsed up the string into its variables, named them, and recombined them and still have the same issue:

Sub RunCut(ByVal cutPT As PivotTable, ByVal cWSN As String)
Dim rPLT As Range, i As Integer
Dim cNM As String, cRNG As Range, c As Range, tws As Worksheet
Dim Frmla As String, NewFrmla As String, RevEndRow As Integer, StatsBegRow As Integer
Dim ws As Worksheet, ordr As Integer
Dim v1 As String, v2l As String, v2v As String, v3l As String, v3v As String


Set rPLT = ThisWorkbook.Worksheets("CutSpammer").Range("L3")
Set tws = ThisWorkbook.Worksheets("CutTemplate")
'" BGT_2012", "MN", "1", "T5", "Material Costs - Standard Material Costs", "PL_Metric", "400000_Food"

v2l = "MN"
v3l = "T5"
v4l = "PL_Metric"

RevEndRow = 23
StatsBegRow = 415

i = 0
Do While Len(rPLT.Offset(i, 0).Formula) > 0
    cNM = rPLT.Offset(i, 1).Value
    Set cRNG = tws.Range(rPLT.Offset(i, 2).Value)
    
    For Each c In cRNG
        If Len(tws.Range("J" & c.Row).Formula) > 2 Then
            If c.Offset(-c.Row + 1, 0).Value <> "" And c.Offset(-c.Row + 2, 0).Value <> "" Then
                    v1 = cNM
                    v2v = Month(c.Offset(-c.Row + 2, 0).Value)
                    v3v = tws.Range("G" & c.Row).Value
                    v4v = tws.Range("K" & c.Row).Value
                    NewFrmla = Chr(34) & v1 & Chr(34) & Chr(44) & Chr(32) & Chr(34) & v2l & Chr(34) & Chr(44) & Chr(32) & Chr(34) & v2v & Chr(34) & Chr(44) & Chr(32) & Chr(34) & v3l & Chr(34) & Chr(44) & Chr(32) & Chr(34) & v3v & Chr(34) & Chr(44) & Chr(32) & Chr(34) & v4l & Chr(34) & Chr(44) & Chr(32) & Chr(34) & v4v & Chr(34)
                    MsgBox NewFrmla
                    If IsError(cutPT.GetPivotData(NewFrmla).Value) = True Then
                        c.Value = 0
                    Else
                        If c.Row < RevEndRow Or c.Row > StatsBegRow Then
                            c.Value = cutPT.GetPivotData(NewFrmla).Value
                        Else
                            c.Value = -1 * cutPT.GetPivotData(NewFrmla).Value
                        End If
                    End If
            End If
        End If
    Next c
    i = i + 1
Loop

End Sub

 

[winglessbuzzard]

a print screen of the message box generated in the code above gives us this:

Microsoft Excel X

"Act_2010", "MN", "1", "T5", "Food", "PL_Metric", "10_Food"



if I type exactly what shows up in the message box, into the NewFrmla on the line of code in question, it works with no problems.

 

[RoryA]

Getpivotdata expects multiple arguments, separated by commas and not one string with commas in it.

 

[winglessbuzzard]

Lol... Thanks Rory!!! As a sidenote, you were awesome in last season's Dr. Who!

FYI - the below code, as Rory suggests, works fine. I used the variables in the GetPivotData formula instead of buiding the GetPivotData formula as a string:

Sub RunCut(ByVal cutPT As PivotTable, ByVal cWSN As String)
Dim rPLT As Range, i As Integer
Dim cNM As String, cRNG As Range, c As Range, tws As Worksheet
Dim Frmla As String, NewFrmla As String, RevEndRow As Integer, StatsBegRow As Integer
Dim ws As Worksheet, ordr As Integer
Dim v1 As String, v2l As String, v2v As String, v3l As String, v3v As String


Set rPLT = ThisWorkbook.Worksheets("CutSpammer").Range("L3")
Set tws = ThisWorkbook.Worksheets("CutTemplate")
'" BGT_2012", "MN", "1", "T5", "Material Costs - Standard Material Costs", "PL_Metric", "400000_Food"

v2l = "MN"
v3l = "T5"
v4l = "PL_Metric"

RevEndRow = 23
StatsBegRow = 415

i = 0
Do While Len(rPLT.Offset(i, 0).Formula) > 0
    cNM = rPLT.Offset(i, 1).Value
    Set cRNG = tws.Range(rPLT.Offset(i, 2).Value)
    
    For Each c In cRNG
        If Len(tws.Range("J" & c.Row).Formula) > 2 Then
            If c.Offset(-c.Row + 1, 0).Value <> "" And c.Offset(-c.Row + 2, 0).Value <> "" Then
                    v1 = cNM
                    v2v = Month(c.Offset(-c.Row + 2, 0).Value)
                    v3v = tws.Range("G" & c.Row).Value
                    v4v = tws.Range("K" & c.Row).Value
                    If IsError(cutPT.GetPivotData(v1, v2l, v2v, v3l, v3v, v4l, v4v).Value) = True Then
                        c.Value = 0
                    Else
                        If c.Row < RevEndRow Or c.Row > StatsBegRow Then
                            c.Value = cutPT.GetPivotData(v1, v2l, v2v, v3l, v3v, v4l, v4v).Value
                        Else
                            c.Value = -1 * cutPT.GetPivotData(v1, v2l, v2v, v3l, v3v, v4l, v4v).Value
                        End If
                    End If
            End If
        End If
    Next c
    i = i + 1
Loop

End Sub