Insert long formula

[Sandy]

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!

 

[Don Guillett]

You might try giving your ranges a name and using that
rngf
rngj
rngi

 

[PCLIVE]

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

 

[Rick Rothstein \(MVP - VB\)]

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

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!