My equation is too long!
 

I am populating a table with equations. However, some of them are too long.
I can type them in manually (and they work), so I know the equation is OK.
What can I do?


Selection.FormulaArray = _
"=SUM(IF(('DIMS Data (2)'!R1C1:R6992C[-2]=""MAG DEV TLP RIG
MOB"")*('DIMS Data (2)'!R1C8:R6992C[5]=" & PN & ")*('DIMS Data
(2)'!$I$1:$I$6992='RISER'),'DIMS Data (2)'!R1C6:R6992C[3]))"


That is only two lines of code

My equation is too long!
 

"OkieViking" wrote in message
...
I am populating a table with equations. However, some of them are too
long.
I can type them in manually (and they work), so I know the equation is OK.
What can I do?


Selection.FormulaArray = _
"=SUM(IF(('DIMS Data (2)'!R1C1:R6992C[-2]=""MAG DEV TLP RIG
MOB"")*('DIMS Data (2)'!R1C8:R6992C[5]=" & PN & ")*('DIMS Data
(2)'!$I$1:$I$6992='RISER'),'DIMS Data (2)'!R1C6:R6992C[3]))"


That is only two lines of code

I don't understand. If you can enter the formula manually, how can it be too
long?
/Fredrik

My equation is too long!
 

Fredrik,

I can type it into the spreadsheet. However, I want a macro to enter it
into the spreadsheet.

Selection.FormulaArray = _
"=SUM(IF(('DIMS Data (2)'!R1C1:R6992C[-2]=""MAG DEV TLP RIG
MOB"")*('DIMS Data (2)'!R1C8:R6992C[5]=" & PN & "),'DIMS Data (2)'
!R1C6:R6992C[3]))"

works in VBA. However the line below doesn't. It gets error messages.

Selection.FormulaArray = _
"=SUM(IF(('DIMS Data (2)'!R1C1:R6992C[-2]=""MAG DEV TLP RIG
MOB"")*('DIMS Data (2)'!R1C8:R6992C[5]=" & PN & ")*('DIMS Data
(2)'!$I$1:$I$6992='RISER'),'DIMS Data (2)'!R1C6:R6992C[3]))"

"Fredrik Wahlgren" wrote:


"OkieViking" wrote in message
...
I am populating a table with equations. However, some of them are too
long.
I can type them in manually (and they work), so I know the equation is OK.
What can I do?


Selection.FormulaArray = _
"=SUM(IF(('DIMS Data (2)'!R1C1:R6992C[-2]=""MAG DEV TLP RIG
MOB"")*('DIMS Data (2)'!R1C8:R6992C[5]=" & PN & ")*('DIMS Data
(2)'!$I$1:$I$6992='RISER'),'DIMS Data (2)'!R1C6:R6992C[3]))"


That is only two lines of code

I don't understand. If you can enter the formula manually, how can it be too
long?
/Fredrik

My equation is too long!
 

for FormulaArray property, I believe the limit is 255 characters. Manually,
the limit is 1024 characters.

--
Regards,
Tom Ogilvy

"OkieViking" wrote in message
...
I am populating a table with equations. However, some of them are too
long.
I can type them in manually (and they work), so I know the equation is OK.
What can I do?


Selection.FormulaArray = _
"=SUM(IF(('DIMS Data (2)'!R1C1:R6992C[-2]=""MAG DEV TLP RIG
MOB"")*('DIMS Data (2)'!R1C8:R6992C[5]=" & PN & ")*('DIMS Data
(2)'!$I$1:$I$6992='RISER'),'DIMS Data (2)'!R1C6:R6992C[3]))"


That is only two lines of code

My equation is too long!
 

"Tom Ogilvy" wrote in message
...
for FormulaArray property, I believe the limit is 255 characters.
Manually,
the limit is 1024 characters.

--
Regards,
Tom Ogilvy


Interesting, I didn't know that. Okie, have you tried to catch the exception
message?

/Fredrik

My equation is too long!
 

Thanks Tom,

The equation is only 181 characters long if I count correctly (within the
quotation marks). Is there a column width limitation in VBA editor? The
equation stretches out into column 192 right now. And if so, where could I
insert the " _" continuation mark to make it continue onto the next line.

"Tom Ogilvy" wrote:

for FormulaArray property, I believe the limit is 255 characters. Manually,
the limit is 1024 characters.

--
Regards,
Tom Ogilvy

"OkieViking" wrote in message
...
I am populating a table with equations. However, some of them are too
long.
I can type them in manually (and they work), so I know the equation is OK.
What can I do?


Selection.FormulaArray = _
"=SUM(IF(('DIMS Data (2)'!R1C1:R6992C[-2]=""MAG DEV TLP RIG
MOB"")*('DIMS Data (2)'!R1C8:R6992C[5]=" & PN & ")*('DIMS Data
(2)'!$I$1:$I$6992='RISER'),'DIMS Data (2)'!R1C6:R6992C[3]))"


That is only two lines of code

My equation is too long!
 

The error message I get is: "Run-time error '1004':
Unable to set the FormulaArray property of the Range class
"Tom Ogilvy" wrote:

for FormulaArray property, I believe the limit is 255 characters. Manually,
the limit is 1024 characters.

--
Regards,
Tom Ogilvy

"OkieViking" wrote in message
...
I am populating a table with equations. However, some of them are too
long.
I can type them in manually (and they work), so I know the equation is OK.
What can I do?


Selection.FormulaArray = _
"=SUM(IF(('DIMS Data (2)'!R1C1:R6992C[-2]=""MAG DEV TLP RIG
MOB"")*('DIMS Data (2)'!R1C8:R6992C[5]=" & PN & ")*('DIMS Data
(2)'!$I$1:$I$6992='RISER'),'DIMS Data (2)'!R1C6:R6992C[3]))"


That is only two lines of code

My equation is too long!
 

MVP Dick Kusleika's website had a simple solution to
overcoming this problem. You can see at:

http://www.dicks-blog.com/archives/2005/01/10/entering-
long-array-formulas-in-vba/

HTH
Jason
Atlanta, GA

-----Original Message-----
I am populating a table with equations. However, some of
them are too long.
I can type them in manually (and they work), so I know
the equation is OK.
What can I do?


Selection.FormulaArray = _
"=SUM(IF(('DIMS Data (2)'!R1C1:R6992C[-2]=""MAG
DEV TLP RIG
MOB"")*('DIMS Data (2)'!R1C8:R6992C[5]=" & PN & ")*('DIMS
Data
(2)'!$I$1:$I$6992='RISER'),'DIMS Data (2)'!R1C6:R6992C
[3]))"


That is only two lines of code
.

My equation is too long!
 

sStr = "=SUM(IF(('DIMS Data (2)'!R1C1:R6992C[-2]=""MAG DEV TLP RIG
MOB"")*('DIMS Data (2)'!R1C8:R6992C[5]=" & PN & ")*('DIMS Data
(2)'!$I$1:$I$6992='RISER'),'DIMS Data (2)'!R1C6:R6992C[3]))"
? len(sStr)
169
? sStr
=SUM(IF(('DIMS Data (2)'!R1C1:R6992C[-2]="MAG DEV TLP RIG MOB")*('DIMS Data
(2)'!R1C8:R6992C[5]=)*('DIMS Data (2)'!
$I$1:$I$6992='RISER'),'DIMS Data (2)'!R1C6:R6992C[3]))

if you look at the last part, you can see that this isn't a legal formula.
RISER has single quotes around it, you have an =)

So you have more work to do to get the formula correct. I suggest you enter
the formula in a cell, select the cell, then go to the immediate window and
do
? activeCell.FormulaR1C1

This should give you the string you need.

--
Regards,
Tom Ogilvy



"OkieViking" wrote in message
...
Thanks Tom,

The equation is only 181 characters long if I count correctly (within the
quotation marks). Is there a column width limitation in VBA editor? The
equation stretches out into column 192 right now. And if so, where could
I
insert the " _" continuation mark to make it continue onto the next line.

"Tom Ogilvy" wrote:

for FormulaArray property, I believe the limit is 255 characters.
Manually,
the limit is 1024 characters.

--
Regards,
Tom Ogilvy

"OkieViking" wrote in message
...
I am populating a table with equations. However, some of them are too
long.
I can type them in manually (and they work), so I know the equation is
OK.
What can I do?


Selection.FormulaArray = _
"=SUM(IF(('DIMS Data (2)'!R1C1:R6992C[-2]=""MAG DEV TLP RIG
MOB"")*('DIMS Data (2)'!R1C8:R6992C[5]=" & PN & ")*('DIMS Data
(2)'!$I$1:$I$6992='RISER'),'DIMS Data (2)'!R1C6:R6992C[3]))"


That is only two lines of code

My equation is too long!
 

I just figured out my problem. Through editing I had ended up with both
range types in the same equation (R1C1 and A1). It works now.

Thanks guys.

Thanks Jason. I will check out the web site.



"Jason Morin" wrote:

MVP Dick Kusleika's website had a simple solution to
overcoming this problem. You can see at:

http://www.dicks-blog.com/archives/2005/01/10/entering-
long-array-formulas-in-vba/

HTH
Jason
Atlanta, GA

-----Original Message-----
I am populating a table with equations. However, some of
them are too long.
I can type them in manually (and they work), so I know
the equation is OK.
What can I do?


Selection.FormulaArray = _
"=SUM(IF(('DIMS Data (2)'!R1C1:R6992C[-2]=""MAG
DEV TLP RIG
MOB"")*('DIMS Data (2)'!R1C8:R6992C[5]=" & PN & ")*('DIMS
Data
(2)'!$I$1:$I$6992='RISER'),'DIMS Data (2)'!R1C6:R6992C
[3]))"


That is only two lines of code
.

My equation is too long!
 

Excellent!

/Fredrik

"OkieViking" wrote in message
...
I just figured out my problem. Through editing I had ended up with both
range types in the same equation (R1C1 and A1). It works now.

Thanks guys.

Thanks Jason. I will check out the web site.



"Jason Morin" wrote:

MVP Dick Kusleika's website had a simple solution to
overcoming this problem. You can see at:

http://www.dicks-blog.com/archives/2005/01/10/entering-
long-array-formulas-in-vba/

HTH
Jason
Atlanta, GA

-----Original Message-----
I am populating a table with equations. However, some of
them are too long.
I can type them in manually (and they work), so I know
the equation is OK.
What can I do?


Selection.FormulaArray = _
"=SUM(IF(('DIMS Data (2)'!R1C1:R6992C[-2]=""MAG
DEV TLP RIG
MOB"")*('DIMS Data (2)'!R1C8:R6992C[5]=" & PN & ")*('DIMS
Data
(2)'!$I$1:$I$6992='RISER'),'DIMS Data (2)'!R1C6:R6992C
[3]))"


That is only two lines of code
.