| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| http://CannotDeleteFile.net - Cannot Delete File? Try Long Path ToolFilename is too long? Computer Complaining Your Filename Is Too Long? TheLong Path Tool Can Help While most people can go about their businessblissfully unaware of the Windo | Excel Discussion (Misc queries) | |||
| Long Long Long Nested If Function | Excel Discussion (Misc queries) | |||
| Clearing cells takes long, long time | Excel Discussion (Misc queries) | |||
| Equation Editor- problem when editing an equation | Excel Discussion (Misc queries) | |||
| I have a long equation which I need to repeat 16 times! | Excel Worksheet Functions | |||
Linear Mode
