Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
getPivotData
I am trying to use a variable in the GetPivotData function and I keep getting
#REF error. Here is my syntax: Cells(lRow, 5).Select ActiveCell.FormulaR1C1 = _ "=GETPIVOTDATA(""Sum of xfers"",Pivot_table!R3C1,""day"",DATE(2005,3,29)" When I try: Cells(lRow, 5).Select ActiveCell.FormulaR1C1 = _ "=GETPIVOTDATA(""Sum of xfers"",Pivot_table!R3C1,""day"",temp3)" using temp3, a variable holding a date, it gives me a #REF error. Can I not use variables here? What can I do to make this dynamic? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
getPivotData
you're hard-coding the word temp3 instead of using it as a variable
const FORMULA as String = _ "=GETPIVOTDATA(""Sum of xfers"",Pivot_table!R3C1,""day"",TEMP3)" dim sFormula as String sFormula = Replace( FORMULA,"TEMP3",temp3) ActiveCell.FormulaR1C1 = sFormula you could simply concatenate it, but the code above is real easy to see and understand and debug. ActiveCell.FormulaR1C1 = _ "=GETPIVOTDATA(""Sum of xfers"",Pivot_table!R3C1,""day""," & temp3 & ")" "KyWilde" wrote: I am trying to use a variable in the GetPivotData function and I keep getting #REF error. Here is my syntax: Cells(lRow, 5).Select ActiveCell.FormulaR1C1 = _ "=GETPIVOTDATA(""Sum of xfers"",Pivot_table!R3C1,""day"",DATE(2005,3,29)" When I try: Cells(lRow, 5).Select ActiveCell.FormulaR1C1 = _ "=GETPIVOTDATA(""Sum of xfers"",Pivot_table!R3C1,""day"",temp3)" using temp3, a variable holding a date, it gives me a #REF error. Can I not use variables here? What can I do to make this dynamic? Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
getPivotData
Thanks Patrick... unfortunately when I use the recommended code I get the
same #REF error. This is really confusing me. Any more suggestions? Thanks! "Patrick Molloy" wrote: you're hard-coding the word temp3 instead of using it as a variable const FORMULA as String = _ "=GETPIVOTDATA(""Sum of xfers"",Pivot_table!R3C1,""day"",TEMP3)" dim sFormula as String sFormula = Replace( FORMULA,"TEMP3",temp3) ActiveCell.FormulaR1C1 = sFormula you could simply concatenate it, but the code above is real easy to see and understand and debug. ActiveCell.FormulaR1C1 = _ "=GETPIVOTDATA(""Sum of xfers"",Pivot_table!R3C1,""day""," & temp3 & ")" "KyWilde" wrote: I am trying to use a variable in the GetPivotData function and I keep getting #REF error. Here is my syntax: Cells(lRow, 5).Select ActiveCell.FormulaR1C1 = _ "=GETPIVOTDATA(""Sum of xfers"",Pivot_table!R3C1,""day"",DATE(2005,3,29)" When I try: Cells(lRow, 5).Select ActiveCell.FormulaR1C1 = _ "=GETPIVOTDATA(""Sum of xfers"",Pivot_table!R3C1,""day"",temp3)" using temp3, a variable holding a date, it gives me a #REF error. Can I not use variables here? What can I do to make this dynamic? Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
getPivotData
the #REF suggests that you're trying to read /use a range that doesn't
exist. "KyWilde" wrote in message ... Thanks Patrick... unfortunately when I use the recommended code I get the same #REF error. This is really confusing me. Any more suggestions? Thanks! "Patrick Molloy" wrote: you're hard-coding the word temp3 instead of using it as a variable const FORMULA as String = _ "=GETPIVOTDATA(""Sum of xfers"",Pivot_table!R3C1,""day"",TEMP3)" dim sFormula as String sFormula = Replace( FORMULA,"TEMP3",temp3) ActiveCell.FormulaR1C1 = sFormula you could simply concatenate it, but the code above is real easy to see and understand and debug. ActiveCell.FormulaR1C1 = _ "=GETPIVOTDATA(""Sum of xfers"",Pivot_table!R3C1,""day""," & temp3 & ")" "KyWilde" wrote: I am trying to use a variable in the GetPivotData function and I keep getting #REF error. Here is my syntax: Cells(lRow, 5).Select ActiveCell.FormulaR1C1 = _ "=GETPIVOTDATA(""Sum of xfers"",Pivot_table!R3C1,""day"",DATE(2005,3,29)" When I try: Cells(lRow, 5).Select ActiveCell.FormulaR1C1 = _ "=GETPIVOTDATA(""Sum of xfers"",Pivot_table!R3C1,""day"",temp3)" using temp3, a variable holding a date, it gives me a #REF error. Can I not use variables here? What can I do to make this dynamic? Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
getPivotData
Try converting the date to a long integer:
"=GETPIVOTDATA(""Sum of xfers"",Pivot_table!R3C1,""day""," & CLng(temp3) & ")" KyWilde wrote: Thanks Patrick... unfortunately when I use the recommended code I get the same #REF error. This is really confusing me. Any more suggestions? Thanks! "Patrick Molloy" wrote: you're hard-coding the word temp3 instead of using it as a variable const FORMULA as String = _ "=GETPIVOTDATA(""Sum of xfers"",Pivot_table!R3C1,""day"",TEMP3)" dim sFormula as String sFormula = Replace( FORMULA,"TEMP3",temp3) ActiveCell.FormulaR1C1 = sFormula you could simply concatenate it, but the code above is real easy to see and understand and debug. ActiveCell.FormulaR1C1 = _ "=GETPIVOTDATA(""Sum of xfers"",Pivot_table!R3C1,""day""," & temp3 & ")" "KyWilde" wrote: I am trying to use a variable in the GetPivotData function and I keep getting #REF error. Here is my syntax: Cells(lRow, 5).Select ActiveCell.FormulaR1C1 = _ "=GETPIVOTDATA(""Sum of xfers"",Pivot_table!R3C1,""day"",DATE(2005,3,29 )" When I try: Cells(lRow, 5).Select ActiveCell.FormulaR1C1 = _ "=GETPIVOTDATA(""Sum of xfers"",Pivot_table!R3C1,""day"",temp3)" using temp3, a variable holding a date, it gives me a #REF error. Can I not use variables here? What can I do to make this dynamic? Thanks. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
GETPIVOTDATA | Excel Discussion (Misc queries) | |||
GETPIVOTDATA | Excel Discussion (Misc queries) | |||
Getpivotdata | Excel Discussion (Misc queries) | |||
GETPIVOTDATA | Excel Worksheet Functions | |||
GETPIVOTDATA | Excel Worksheet Functions |