If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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! . |
Thread Tools | |
Display Modes | |
|
|