|
Insert long formula
Hello
I am trying to insert a long formula via code and I am having trouble with the line continuations " _" I have tried many combination but I am still getting errors. This is the formula: =SUMPRODUCT(--($F$3:$F$500="7-F"),--($J$3:$J$500="A"),--($H$3:$H$500="DOG"),--($i$3:$i$500<""),--(ISERROR(MATCH($C$3:$C$500,'G:\Mypath\[myfile.xls]Sheet4'!$A$1:$A$500,0))),roundup(1.02*(k$3:k$500), 0))+SUMPRODUCT(--($F$3:$F$500="7-F"),--($J$3:$J$500="A"),--($H$3:$H$500="DOGandCAT"),--($i$3:$i$500<),--(ISERROR(MATCH($C$3:$C$500,'G:\Mypath\[myfile.xls]Sheet4'!$A$1:$A$500,0))),roundup(1.02*(k$3:k$500), 0))+ SUMPRODUCT(--($F$3:$F$500="1-NS"),--($J$3:$J$500="S"),--($H$3:$H$500="DOG"),--($i$3:$i$500<""),--(ISERROR(MATCH($C$3:$C$500,'G:\mypath\[myfile.xls]Sheet4'!$A$1:$A$500,0))),roundup(1.02*(k$3:k$500), 0))+SUMPRODUCT(--($F$3:$F$500="1-NS"),--($J$3:$J$500="S"),--($H$3:$H$500="DOGandCat"),--($i$3:$i$500<""),--(ISERROR(MATCH($C$3:$C$500,'G:\mypath\[myfile.xls]Sheet4'!$A$1:$A$500,0))), roundup(1.02*(k$3:k$500),0)) Its a long formula and a cleanup of the formula may help. Some guidance of the use of the " _" would be appreciated as well. Thanks! |
Insert long formula
|
Insert long formula
Try this (untested).
=SUMPRODUCT(--($F$3:$F$500="7-F"),--($J$3:$J$500="A"),--($H$3:$H$500="DOG")+($H$3:$H$500="DOGandCAT"),--($i$3:$i$500<""),--(ISERROR(MATCH($C$3:$C$500,'G:\Mypath\[myfile.xls]Sheet4'!$A$1:$A$500,0))),roundup(1.02*(k$3:k$500), 0))+ SUMPRODUCT(--($F$3:$F$500="1-NS"),--($J$3:$J$500="S"),--($H$3:$H$500="DOG")+($H$3:$H$500="DOGandCat"),--($i$3:$i$500<""),--(ISERROR(MATCH($C$3:$C$500,'G:\mypath\[myfile.xls]Sheet4'!$A$1:$A$500,0))),roundup(1.02*(k$3:k$500), 0)) Regards, Paul -- "Sandy" wrote in message ... Hello I am trying to insert a long formula via code and I am having trouble with the line continuations " _" I have tried many combination but I am still getting errors. This is the formula: =SUMPRODUCT(--($F$3:$F$500="7-F"),--($J$3:$J$500="A"),--($H$3:$H$500="DOG"),--($i$3:$i$500<""),--(ISERROR(MATCH($C$3:$C$500,'G:\Mypath\[myfile.xls]Sheet4'!$A$1:$A$500,0))),roundup(1.02*(k$3:k$500), 0))+SUMPRODUCT(--($F$3:$F$500="7-F"),--($J$3:$J$500="A"),--($H$3:$H$500="DOGandCAT"),--($i$3:$i$500<),--(ISERROR(MATCH($C$3:$C$500,'G:\Mypath\[myfile.xls]Sheet4'!$A$1:$A$500,0))),roundup(1.02*(k$3:k$500), 0))+ SUMPRODUCT(--($F$3:$F$500="1-NS"),--($J$3:$J$500="S"),--($H$3:$H$500="DOG"),--($i$3:$i$500<""),--(ISERROR(MATCH($C$3:$C$500,'G:\mypath\[myfile.xls]Sheet4'!$A$1:$A$500,0))),roundup(1.02*(k$3:k$500), 0))+SUMPRODUCT(--($F$3:$F$500="1-NS"),--($J$3:$J$500="S"),--($H$3:$H$500="DOGandCat"),--($i$3:$i$500<""),--(ISERROR(MATCH($C$3:$C$500,'G:\mypath\[myfile.xls]Sheet4'!$A$1:$A$500,0))), roundup(1.02*(k$3:k$500),0)) Its a long formula and a cleanup of the formula may help. Some guidance of the use of the " _" would be appreciated as well. Thanks! |
Insert long formula
Since you didn't post your code where you are assigning the formula, it is
hard to know if everything you showed us is a String constant or is the result of concatenating String constants with Sting variables. Let me give you some examples of the process and see if that helps you or not. One of the key things to using line continuations is that you **cannot** put them **inside** a String constant, which is basically what the bulk of your formula assignment would be; rather, you will have to break the text String constant into two or more concatenated sub-Strings constants and line continue them at the concatenation points. Here is an example. Original Text ========================================== X = "A long line of text that we will put a line continuation in." Intermediate Step (for illustration only) ========================================== X = "A long line of text that " & "we will put a line continuation in." Continued Line ========================================== X = "A long line of text that " & _ "we will put a line continuation in." Notice that when doing this, attention must be paid to preserving blank spaces (note the preserved blank space between the words "that" and "we" in the original text.) With that said, if your text contains a concatenated variable, you can of course line-continue it at that point. For example. Original Text ========================================== X = "Beginning Text " & MyTextVariable & " followed by rest of text" Continued Line - Possibility 1 ========================================== X = "Beginning Text " & _ MyTextVariable & " followed by rest of text" Continued Line - Possibility 2 ========================================== X = "Beginning Text " & MyTextVariable & _ " followed by rest of text" As you can see, whether you need to break apart the text String constant parts or not depends on if you already have some already existing concatenation points due to variable concatenations.... so, it is impossible to tell you what you need to do without seeing how you are composing the formula inside your code (that is, are there concatenated variables or not). Rick "Sandy" wrote in message ... Hello I am trying to insert a long formula via code and I am having trouble with the line continuations " _" I have tried many combination but I am still getting errors. This is the formula: =SUMPRODUCT(--($F$3:$F$500="7-F"),--($J$3:$J$500="A"),--($H$3:$H$500="DOG"),--($i$3:$i$500<""),--(ISERROR(MATCH($C$3:$C$500,'G:\Mypath\[myfile.xls]Sheet4'!$A$1:$A$500,0))),roundup(1.02*(k$3:k$500), 0))+SUMPRODUCT(--($F$3:$F$500="7-F"),--($J$3:$J$500="A"),--($H$3:$H$500="DOGandCAT"),--($i$3:$i$500<),--(ISERROR(MATCH($C$3:$C$500,'G:\Mypath\[myfile.xls]Sheet4'!$A$1:$A$500,0))),roundup(1.02*(k$3:k$500), 0))+ SUMPRODUCT(--($F$3:$F$500="1-NS"),--($J$3:$J$500="S"),--($H$3:$H$500="DOG"),--($i$3:$i$500<""),--(ISERROR(MATCH($C$3:$C$500,'G:\mypath\[myfile.xls]Sheet4'!$A$1:$A$500,0))),roundup(1.02*(k$3:k$500), 0))+SUMPRODUCT(--($F$3:$F$500="1-NS"),--($J$3:$J$500="S"),--($H$3:$H$500="DOGandCat"),--($i$3:$i$500<""),--(ISERROR(MATCH($C$3:$C$500,'G:\mypath\[myfile.xls]Sheet4'!$A$1:$A$500,0))), roundup(1.02*(k$3:k$500),0)) Its a long formula and a cleanup of the formula may help. Some guidance of the use of the " _" would be appreciated as well. Thanks! |
| All times are GMT +1. The time now is 05:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com