Formula causing run-time error

[cherman]

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.

Here is some more code that runs earlier in my procedure, in case it helps.
There's more code but I don't want to put more in this post than what is
needed.

Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
ReportDir = DLookup("[report_dir]", "tbl_Reports", "[report_name] =
'Escalated Trend Chart Template File'")
objXL.workbooks.Open (ReportDir & "Escalated_Trend_Chart_Template.xls")
Set xlWB = objXL.workbooks
Set xls = objXL.Worksheets("ET_Chart_Data")
xls.Activate

Any idea on how I can solve this problem?

Thanks in advance for your help!

 

[Brad E.]

Cherman - When I put formulas into a cell, I address the formula and not the
value.
xls.cells(Rw, Col + lngColumn).FORMULA =
It may not help, but it should be a simple test to try it.
-- Brad E.

 

[cherman]

Thanks for the idea, but unfortunately it didn't work. I've further tested by
replacing the 'Feature Request' with a number so the end of my forumula looks
like this

--(ET_Raw_Data!$D$2:ET_Raw_Data!$D$20000=4))

It works like a charm, so this is telling me the problem has to lie with
using the single quotes ('Feature Request').

Any suggestions on how to alter this?

Thanks!!

Cherman - When I put formulas into a cell, I address the formula and not the
value.
xls.cells(Rw, Col + lngColumn).FORMULA =
It may not help, but it should be a simple test to try it.
-- Brad E.

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.

Here is some more code that runs earlier in my procedure, in case it helps.
There's more code but I don't want to put more in this post than what is
needed.

Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
ReportDir = DLookup("[report_dir]", "tbl_Reports", "[report_name] =
'Escalated Trend Chart Template File'")
objXL.workbooks.Open (ReportDir & "Escalated_Trend_Chart_Template.xls")
Set xlWB = objXL.workbooks
Set xls = objXL.Worksheets("ET_Chart_Data")
xls.Activate

Any idea on how I can solve this problem?

Thanks in advance for your help!