Formula causing run-time error in Excel Programming
That's what I needed! Thank you very much!!
"Per Jessen" wrote:
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" skrev i meddelelsen
...
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_Dat a!$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!
.
|