Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula causing run-time error in Excel Programming
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula causing run-time error in Excel Programming
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What's causing error message? | Excel Worksheet Functions | |||
Late binding to Excel from Access causing Object error | Excel Discussion (Misc queries) | |||
Query causing #ref error in spreadsheet | Excel Discussion (Misc queries) | |||
VLOOKUP Formula causing an error | Excel Discussion (Misc queries) | |||
Formula Causing a Save Error Message | Excel Worksheet Functions |