A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Formula causing run-time error in Excel Programming



 
 
Thread Tools Display Modes
  #1  
Old March 15th, 2010, 09:03 PM posted to microsoft.public.excel.worksheet.functions
cherman
external usenet poster
 
Posts: 25
Default 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  
Old March 15th, 2010, 09:31 PM posted to microsoft.public.excel.worksheet.functions
Per Jessen
external usenet poster
 
Posts: 686
Default 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  
Old March 15th, 2010, 11:59 PM posted to microsoft.public.excel.worksheet.functions
cherman
external usenet poster
 
Posts: 25
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:44 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.