1004 Application or Object Defined Error 1

[bajo71]

Hello,

I am trying to assignt the following formula to a cell, but keep getting above error on the last line...Cells(i, 5).Formula = strFormula. I've tried with and without Formula property and it does not work. Hard coding values or a simple "=a1+b1" works, but for some reason does not like the strFormula variable.

Any help is most appreciated.

With Sheets("Emdeon Inc_Post eRX (2)")
FinalRow = .Cells(Rows.Count, 1).End(xlUp).Row

'For iShtCount = 1 To 3
'shtName = "Emdeon Inc_Prior to eRX"
For i = 8 To FinalRow
If .Cells(i, 1) <> "" And Right(.Cells(i, 1), 6) <> "Total" Then AcctFamil = .Cells(i, 1)

If .Cells(i, 2) <> "" And Right(.Cells(i, 2), 6) <> "Total" Then AcctCat = .Cells(i, 2)

If .Cells(i, 1) <> "" And Right(.Cells(i, 3), 6) <> "Total" Then Rollup2 = .Cells(i, 3)

If Cells(i, 4) <> "" Then AcctName = Cells(i, 4).Address


strFormula = "GETPIVOTDATA(' JUL-09','Emdeon Inc_Prior to eRX'!$A$6,'ACCT_FAMIL'," & "'" & AcctFamil & "'" & ",'ACCT_CAT'," & "'" & AcctCat & _
"'" & "'ROLLUP2'," & "'" & Rollup2 & "'" & ",'ACCT_NAME'," & "'" & AcctName & "'" & ")),0,"
strFormula = "=IF(ISERROR(" & strFormula & Left(strFormula & strFormula, Len(strFormula) - 3)

.Cells(i, 5).Formula = strFormula 'Change column to reflect proper spreadsheet source

 

[hmckillop]

a couple of pointers,
- try debug.print on the strFormula and see if the formula can be paste into the cell
- try assigning a simple formula e.g. "=sum(1,2)" - this will point to the syntax of build of the statement being the problem

Without having all the reference data its hard to test.



"I'm living so far beyond my income that we may almost be said to be living apart

 

[PHV]

Did you even Debug.Print strFormula ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bajo71]

PHV: Yes I did. In fact, if I copy a cell formula from the app and assign strFormula to it, it causes the same error. I will say this much. The "" versus ' issue is somewhat confounding. VBA does not like all the double quotes going on, so I changed them to single, except for the outliers. Nevertheless it does not like the formula.

 

[PHV]

Anyway, I'd replace this:
If .Cells(i, 1) <> "" And Right(.Cells(i, 3), 6) <> "Total" Then Rollup2 = .Cells(i, 3)
with this:
If .Cells(i, [!]3[!]) <> "" And Right(.Cells(i, 3), 6) <> "Total" Then Rollup2 = .Cells(i, 3)

Could you please post the output of the Debug.Print strFormula and a corresponding working formula from the GUI ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bajo71]

?Debug.Print strFormula
=IF(ISERROR(GETPIVOTDATA(' JUL-09','Emdeon Inc_Prior to eRX'!$A$6,'ACCT_FAMIL','Balance Sheet','ACCT_CAT','Assets''ROLLUP2','Cash & Cash Equivalents','ACCT_NAME','$D$8')),0,GETPIVOTDATA(' JUL-09','Emdeon Inc_Prior to eRX'!$A$6,'ACCT_FAMIL','Balance Sheet','ACCT_CAT','Assets''ROLLUP2','Cash & Cash Equivalents','ACCT_NAME','$D$8'))

GUI formula:
=IF(ISERROR(GETPIVOTDATA(" JUL-09",'Emdeon Inc_Prior to eRX'!$A$6,"ACCT_FAMIL",$A$8,"ACCT_CAT","Assets","ROLLUP2","Cash & Cash Equivalents","ACCT_NAME",$D9)),0,GETPIVOTDATA(" JUL-09",'Emdeon Inc_Prior to eRX'!$A$6,"ACCT_FAMIL",$A$8,"ACCT_CAT","Assets","ROLLUP2","Cash & Cash Equivalents","ACCT_NAME",$D9))

For the life of me I can't get the double quotes represented in VBA and I think this is crucial in the GUI.

 

[HarleyQuinn]

You can use Chr(34) to represent a double quote in scenario's like this.

HarleyQuinn
---------------------------------
Black coat, white shoes, black hat, cadillac. The boy's a timebomb!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[bajo71]

OK, I tried the line you suggested:
If .Cells(i, [!]3[!]) <> "" And Right(.Cells(i, 3), 6) <> "Total" Then Rollup2 = .Cells(i, 3)

but caused a syntax error. What is the purpose of [!]? I've never seen that convention.

 

[PHV]

Sorry, it was an error with the TGML tags:
If .Cells(i, [!]3[/!]) <> "" And Right(.Cells(i, 3), 6) <> "Total" Then Rollup2 = .Cells(i, 3)

To get this:
=IF(ISERROR(GETPIVOTDATA(" JUL-09",'Emdeon Inc_Prior to eRX'!$A$6,"ACCT_FAMIL",...
use this in VBA:

strFormula = "=IF(ISERROR(GETPIVOTDATA("" JUL-09"",'Emdeon Inc_Prior to eRX'!$A$6,""ACCT_FAMIL"",..."

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bajo71]

Gotcha! it is indeed column 3. Thanks for the catch.