Formula causing run-time error in Excel Programming

[cherman]

This is a repost.

I have the following line of code that I am trying to drop in a cell via
Access VBA but I keep getting a Run-time error 1004, Application-defined or
object-defined error.

xls.cells(Rw, Col + lngColumn).value =
"=SUMPRODUCT(SUBTOTAL(9,OFFSET(ET_Raw_Data!$C$2:ET_Raw_Data!$C$20000,ROW(ET_Raw_Data!$C$2:ET_Raw_Data!$C$20000)-ROW(ET_Raw_Data!$C$2),0,1)),--(ET_Raw_Data!$A$2:ET_Raw_Data!$A$20000=A"
& lngColumn + 1 & "),--(ET_Raw_Data!$D$2:ET_Raw_Data!$D$20000='Feature
Request'))"

I already have 2 other formulas that are being added to the sheet just fine,
so I suspect the error is because of the single quotes at the end of the
line. BTW, I did have this formula hardcoded on the sheet and I was using
double quotes before I decided to populate my formulas dynamically. However,
Access doesn't like the double quotes.

Oh and using xls.cells(Rw, Col + lngColumn).formula = ... doesn't make a
difference.

Any idea on how I can solve this problem?

Thanks in advance for your help!

 

[Per Jessen]

Hi

I assume that the 'xls' variable is refering to a worksheet object.

You are right, there is a double quote issue:

DblQuote = """"
myformula = _
"=SUMPRODUCT(SUBTOTAL(9,OFFSET(ET_Raw_Data!$C$2:$C$20000,ROW(ET_Raw_Data!$C$2:$C$20000)-ROW(ET_Raw_Data!$C$2),0,1)),--(ET_Raw_Data!$A$2:$A$20000=A"
& LngColumn + 1 & "),--(ET_Raw_Data!$D$2:$D$20000=" & DblQuote & "Feature
Request " & DblQuote & "))"
xls.Cells(rw, col + LngColumn).Formula = myformula

Hopes this helps
....
Per

 

[cherman]

That's what I needed! Thank you very much!!

Hi

I assume that the 'xls' variable is refering to a worksheet object.

You are right, there is a double quote issue:

DblQuote = """"
myformula = _
"=SUMPRODUCT(SUBTOTAL(9,OFFSET(ET_Raw_Data!$C$2:$C$20000,ROW(ET_Raw_Data!$C$2:$C$20000)-ROW(ET_Raw_Data!$C$2),0,1)),--(ET_Raw_Data!$A$2:$A$20000=A"
& LngColumn + 1 & "),--(ET_Raw_Data!$D$2:$D$20000=" & DblQuote & "Feature
Request " & DblQuote & "))"
xls.Cells(rw, col + LngColumn).Formula = myformula

Hopes this helps
....
Per



.