Good morning to all.

I have made a macro that works pretty well with one exception so far.
I have a sumproduct equation that doesn't accept certain characters in VBA.
I've tried a variety of ways to use chr() in line, but I keep getting various
errors.
I've used the three following variations.

&chr(n)& &chr(n_a)& &chr(n_b)& 'this one returns a 1004 error.

&chr(n) & chr(n_a) & chr(n_b) & ' this one returns a missing & error.

& chr(n), & chr(n_a), & chr(n_b) & 'this one returns a 1004 error. I think
it's the commas.

where n, n_a, and n_b are different numeric values for their respective
characters.

What I'd like to do is the following.

ActiveCell.FormulaR1C1=
"=sumproduct((WkshtNm!$Col$RwRngA&""=$ColRw&"")*(W kshtNm!$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))"

At first the only problem I had was the two dbl quotes. I replaced them with
chr(34). I then found that the ampersand symbol was not accepted and drew
another error response. So I tried replacing that with a chr(38), but then I
found that it would not take three characters in a row, together. Or perhaps
I should say that it did not accept it the way I did it-- shown above.
1- can I use multiple chr() in line, together?
2- if so, how?
3- if not, any ideas on how to do this so it would work?

Thank you.

Hi Steve,

give a try to :

ActiveCell.FormulaR1C1 =
"=sumproduct((WkshtNm!$Col$RwRngA=$ColRw)*(WkshtNm !$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))"

(no space & no linefeed between the quotes)
--
Herve Hanuise
http://www.affordsol.be


"SteveDB1" wrote:

Good morning to all.

I have made a macro that works pretty well with one exception so far.
I have a sumproduct equation that doesn't accept certain characters in VBA.
I've tried a variety of ways to use chr() in line, but I keep getting various
errors.
I've used the three following variations.

&chr(n)& &chr(n_a)& &chr(n_b)& 'this one returns a 1004 error.

&chr(n) & chr(n_a) & chr(n_b) & ' this one returns a missing & error.

& chr(n), & chr(n_a), & chr(n_b) & 'this one returns a 1004 error. I think
it's the commas.

where n, n_a, and n_b are different numeric values for their respective
characters.

What I'd like to do is the following.

ActiveCell.FormulaR1C1=
"=sumproduct((WkshtNm!$Col$RwRngA&""=$ColRw&"")*(W kshtNm!$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))"

At first the only problem I had was the two dbl quotes. I replaced them with
chr(34). I then found that the ampersand symbol was not accepted and drew
another error response. So I tried replacing that with a chr(38), but then I
found that it would not take three characters in a row, together. Or perhaps
I should say that it did not accept it the way I did it-- shown above.
1- can I use multiple chr() in line, together?
2- if so, how?
3- if not, any ideas on how to do this so it would work?

Thank you.

Herve,
I've tried that before and the lack of the &"" for the first array causes
the sumproduct-- in my use of it-- to fail.
Which was why I wanted/needed to use some form of the three characters
together. Hence my post.
Thanks though.


"affordsol" wrote:

Hi Steve,

give a try to :

ActiveCell.FormulaR1C1 =
"=sumproduct((WkshtNm!$Col$RwRngA=$ColRw)*(WkshtNm !$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))"

(no space & no linefeed between the quotes)
--
Herve Hanuise
http://www.affordsol.be


"SteveDB1" wrote:

Good morning to all.

I have made a macro that works pretty well with one exception so far.
I have a sumproduct equation that doesn't accept certain characters in VBA.
I've tried a variety of ways to use chr() in line, but I keep getting various
errors.
I've used the three following variations.

&chr(n)& &chr(n_a)& &chr(n_b)& 'this one returns a 1004 error.

&chr(n) & chr(n_a) & chr(n_b) & ' this one returns a missing & error.

& chr(n), & chr(n_a), & chr(n_b) & 'this one returns a 1004 error. I think
it's the commas.

where n, n_a, and n_b are different numeric values for their respective
characters.

What I'd like to do is the following.

ActiveCell.FormulaR1C1=
"=sumproduct((WkshtNm!$Col$RwRngA&""=$ColRw&"")*(W kshtNm!$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))"

At first the only problem I had was the two dbl quotes. I replaced them with
chr(34). I then found that the ampersand symbol was not accepted and drew
another error response. So I tried replacing that with a chr(38), but then I
found that it would not take three characters in a row, together. Or perhaps
I should say that it did not accept it the way I did it-- shown above.
1- can I use multiple chr() in line, together?
2- if so, how?
3- if not, any ideas on how to do this so it would work?

Thank you.

What I'd like to do is the following.

ActiveCell.FormulaR1C1=
"=sumproduct((WkshtNm!$Col$RwRngA&""=$ColRw&"")*(W kshtNm!$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))"

The problem with the above is you are trying to do your concatenation inside
of a pair of double quotes (the ones before the equal sign and after the
last closing parenthesis). Anything contained within those two outer quote
marks is treated simply as text (with the exception of an internal double
quote as you have discovered); so, your variable names and ampersands are
just being treated as simple text. The key is to break the above apart so
the ampersands link text substrings. Assuming I am reading what your
intended correctly (that is, I have figured out which parts of the above are
actually variable names), try this statement instead of the above one...

ActiveCell.FormulaR1C1 = "=SUMPRODUCT((" & WkshtNm & "!$" & col & _
"$" & RwRngA & "=$" & ColRw & ")*(" & WkshtNm & _
"!$" & col & "$" & RwRngB & "=$" & ColRw & _
")*(" & WkshtNm & "!$" & col & "$" & RwRngC & "))"

Rick


"SteveDB1" wrote in message
...
Good morning to all.

I have made a macro that works pretty well with one exception so far.
I have a sumproduct equation that doesn't accept certain characters in
VBA.
I've tried a variety of ways to use chr() in line, but I keep getting
various
errors.
I've used the three following variations.

&chr(n)& &chr(n_a)& &chr(n_b)& 'this one returns a 1004 error.

&chr(n) & chr(n_a) & chr(n_b) & ' this one returns a missing & error.

& chr(n), & chr(n_a), & chr(n_b) & 'this one returns a 1004 error. I think
it's the commas.

where n, n_a, and n_b are different numeric values for their respective
characters.

What I'd like to do is the following.

ActiveCell.FormulaR1C1=
"=sumproduct((WkshtNm!$Col$RwRngA&""=$ColRw&"")*(W kshtNm!$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))"

At first the only problem I had was the two dbl quotes. I replaced them
with
chr(34). I then found that the ampersand symbol was not accepted and drew
another error response. So I tried replacing that with a chr(38), but then
I
found that it would not take three characters in a row, together. Or
perhaps
I should say that it did not accept it the way I did it-- shown above.
1- can I use multiple chr() in line, together?
2- if so, how?
3- if not, any ideas on how to do this so it would work?

Thank you.

Rick,
I just figured out what you're talking about.
Please allow me to elaborate further.

When I inserted my chr() elements, I would end one part with a dbl quote,
insert my &chr()&..... and then start the next portion with a dbl quote
again, all the way through.
E.g.,

ActiveCell.FormulaR1C1=_
"=sumproduct((WkShtNm!$Col$RwRng" & chr(n) &_
&chr(n_a)& & chr(n_b) & "=$ColRw" & chr(n) & &chr(n_a)& & chr(n_b) &_
")*(....contents......)*(.....contents.....))"

where .....contents..... would be a continuation of my previously stated
contents.

and where I have the

& chr(n) & &chr(n_a)& & chr(n_b) &

all in a line together, I get a compile error telling me that a statement is
expected, or some other compile error response, or the 1004 error I
mentioned.

I hope this makes it clearer as to what I've done, and am attempting to do.
Thanks again for the responses.


"Rick Rothstein (MVP - VB)" wrote:

What I'd like to do is the following.

ActiveCell.FormulaR1C1=
"=sumproduct((WkshtNm!$Col$RwRngA&""=$ColRw&"")*(W kshtNm!$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))"

The problem with the above is you are trying to do your concatenation inside
of a pair of double quotes (the ones before the equal sign and after the
last closing parenthesis). Anything contained within those two outer quote
marks is treated simply as text (with the exception of an internal double
quote as you have discovered); so, your variable names and ampersands are
just being treated as simple text. The key is to break the above apart so
the ampersands link text substrings.

ActiveCell.FormulaR1C1 = "=SUMPRODUCT((" & WkshtNm & "!$" & col & _
"$" & RwRngA & "=$" & ColRw & ")*(" & WkshtNm & _
"!$" & col & "$" & RwRngB & "=$" & ColRw & _
")*(" & WkshtNm & "!$" & col & "$" & RwRngC & "))"

Rick


"SteveDB1" wrote in message
...
Good morning to all.

I have made a macro that works pretty well with one exception so far.
I have a sumproduct equation that doesn't accept certain characters in
VBA.
I've tried a variety of ways to use chr() in line, but I keep getting
various
errors.
I've used the three following variations.

&chr(n)& &chr(n_a)& &chr(n_b)& 'this one returns a 1004 error.

&chr(n) & chr(n_a) & chr(n_b) & ' this one returns a missing & error.

& chr(n), & chr(n_a), & chr(n_b) & 'this one returns a 1004 error. I think
it's the commas.

where n, n_a, and n_b are different numeric values for their respective
characters.

What I'd like to do is the following.

ActiveCell.FormulaR1C1=
"=sumproduct((WkshtNm!$Col$RwRngA&""=$ColRw&"")*(W kshtNm!$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))"

At first the only problem I had was the two dbl quotes. I replaced them
with
chr(34). I then found that the ampersand symbol was not accepted and drew
another error response. So I tried replacing that with a chr(38), but then
I
found that it would not take three characters in a row, together. Or
perhaps
I should say that it did not accept it the way I did it-- shown above.
1- can I use multiple chr() in line, together?
2- if so, how?
3- if not, any ideas on how to do this so it would work?

Thank you.

You have an overabundance of ampersands in your code. You only need one to
concatenate two strings.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"SteveDB1" wrote in message
...
Rick,
I just figured out what you're talking about.
Please allow me to elaborate further.

When I inserted my chr() elements, I would end one part with a dbl quote,
insert my &chr()&..... and then start the next portion with a dbl quote
again, all the way through.
E.g.,

ActiveCell.FormulaR1C1=_
"=sumproduct((WkShtNm!$Col$RwRng" & chr(n) &_
&chr(n_a)& & chr(n_b) & "=$ColRw" & chr(n) & &chr(n_a)& & chr(n_b) &_
")*(....contents......)*(.....contents.....))"

where .....contents..... would be a continuation of my previously stated
contents.

and where I have the

& chr(n) & &chr(n_a)& & chr(n_b) &

all in a line together, I get a compile error telling me that a statement
is
expected, or some other compile error response, or the 1004 error I
mentioned.

I hope this makes it clearer as to what I've done, and am attempting to
do.
Thanks again for the responses.


"Rick Rothstein (MVP - VB)" wrote:

What I'd like to do is the following.

ActiveCell.FormulaR1C1=
"=sumproduct((WkshtNm!$Col$RwRngA&""=$ColRw&"")*(W kshtNm!$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))"

The problem with the above is you are trying to do your concatenation
inside
of a pair of double quotes (the ones before the equal sign and after the
last closing parenthesis). Anything contained within those two outer
quote
marks is treated simply as text (with the exception of an internal double
quote as you have discovered); so, your variable names and ampersands are
just being treated as simple text. The key is to break the above apart so
the ampersands link text substrings.

ActiveCell.FormulaR1C1 = "=SUMPRODUCT((" & WkshtNm & "!$" & col & _
"$" & RwRngA & "=$" & ColRw & ")*(" & WkshtNm &
_
"!$" & col & "$" & RwRngB & "=$" & ColRw & _
")*(" & WkshtNm & "!$" & col & "$" & RwRngC &
"))"

Rick


"SteveDB1" wrote in message
...
Good morning to all.

I have made a macro that works pretty well with one exception so far.
I have a sumproduct equation that doesn't accept certain characters in
VBA.
I've tried a variety of ways to use chr() in line, but I keep getting
various
errors.
I've used the three following variations.

&chr(n)& &chr(n_a)& &chr(n_b)& 'this one returns a 1004 error.

&chr(n) & chr(n_a) & chr(n_b) & ' this one returns a missing & error.

& chr(n), & chr(n_a), & chr(n_b) & 'this one returns a 1004 error. I
think
it's the commas.

where n, n_a, and n_b are different numeric values for their respective
characters.

What I'd like to do is the following.

ActiveCell.FormulaR1C1=
"=sumproduct((WkshtNm!$Col$RwRngA&""=$ColRw&"")*(W kshtNm!$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))"

At first the only problem I had was the two dbl quotes. I replaced them
with
chr(34). I then found that the ampersand symbol was not accepted and
drew
another error response. So I tried replacing that with a chr(38), but
then
I
found that it would not take three characters in a row, together. Or
perhaps
I should say that it did not accept it the way I did it-- shown above.
1- can I use multiple chr() in line, together?
2- if so, how?
3- if not, any ideas on how to do this so it would work?

Thank you.

I forgot to say something in my last response to you.

So, all that said, is there a way that I can string a series of
& chr() & together to get all of the characters I need, without having to
enter them manually after the macro has run?

There are times when I'd like to also include some function that I've dim'd
as well.

E.g.,

ActiveCell.FormulaR1C1= "=sumproduct((......" & MyRng & & chr(n) &_
& chr(n_a) & & chr(n_b) & "....................."

Thank you.

Just to muddy the waters a bit more.

The formula doesn't look like it is written in R1C1 Reference style. And
depending on what wkshtnm contains, it may require surrounding apostrophes:

ActiveCell.Formula = "=SUMPRODUCT(('" & WkshtNm & "'!$" & col & _
"$" & RwRngA & "=$" & ColRw & ")*('" & WkshtNm & _
"'!$" & col & "$" & RwRngB & "=$" & ColRw & _
")*('" & WkshtNm & "'!$" & col & "$" & RwRngC & "))"

But that's just a guess.

If it doesn't help the OP, maybe he could post back with what each of those
variables contains--real data that can be plugged into a test macro.


"Rick Rothstein (MVP - VB)" wrote:

What I'd like to do is the following.

ActiveCell.FormulaR1C1=
"=sumproduct((WkshtNm!$Col$RwRngA&""=$ColRw&"")*(W kshtNm!$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))"

The problem with the above is you are trying to do your concatenation inside
of a pair of double quotes (the ones before the equal sign and after the
last closing parenthesis). Anything contained within those two outer quote
marks is treated simply as text (with the exception of an internal double
quote as you have discovered); so, your variable names and ampersands are
just being treated as simple text. The key is to break the above apart so
the ampersands link text substrings. Assuming I am reading what your
intended correctly (that is, I have figured out which parts of the above are
actually variable names), try this statement instead of the above one...

ActiveCell.FormulaR1C1 = "=SUMPRODUCT((" & WkshtNm & "!$" & col & _
"$" & RwRngA & "=$" & ColRw & ")*(" & WkshtNm & _
"!$" & col & "$" & RwRngB & "=$" & ColRw & _
")*(" & WkshtNm & "!$" & col & "$" & RwRngC & "))"

Rick

"SteveDB1" wrote in message
...
Good morning to all.

I have made a macro that works pretty well with one exception so far.
I have a sumproduct equation that doesn't accept certain characters in
VBA.
I've tried a variety of ways to use chr() in line, but I keep getting
various
errors.
I've used the three following variations.

&chr(n)& &chr(n_a)& &chr(n_b)& 'this one returns a 1004 error.

&chr(n) & chr(n_a) & chr(n_b) & ' this one returns a missing & error.

& chr(n), & chr(n_a), & chr(n_b) & 'this one returns a 1004 error. I think
it's the commas.

where n, n_a, and n_b are different numeric values for their respective
characters.

What I'd like to do is the following.

ActiveCell.FormulaR1C1=
"=sumproduct((WkshtNm!$Col$RwRngA&""=$ColRw&"")*(W kshtNm!$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))"

At first the only problem I had was the two dbl quotes. I replaced them
with
chr(34). I then found that the ampersand symbol was not accepted and drew
another error response. So I tried replacing that with a chr(38), but then
I
found that it would not take three characters in a row, together. Or
perhaps
I should say that it did not accept it the way I did it-- shown above.
1- can I use multiple chr() in line, together?
2- if so, how?
3- if not, any ideas on how to do this so it would work?

Thank you.



--

Dave Peterson

Can do Dave,

=sumproduct((APN!$E$5:$E$200&""=$A14&"")*(APN!$F$5 :$F$200=$C14)*(APN!$C$5:$C$200))

When the row, and column does not show the sheet name, it's the sheet on
which I have placed the sumproduct equation.
What I would LIKE to be able to do is to have a series of input boxes that
allow me to choose the variable source rows from the APN! worksheet. I.e., I
never know from one workbook to the next-- we have ~780 workbooks that are
regularly updated-- what the start row of the APN worksheet is going to be.
I've seen then as low as 3, and as high as 8.
I'd then like to be able to set the variable test (=$A14&"", or =$C14) row
location as well. The columns are always the same, but the start rows will
vary-- again, as low as 8, and as high as 19.

The first two arrays are my criteria, and the last of the 3 arrays is my
tally if it finds anything that returns a true from the first two arrays.

I've been tinkering as we post back and forth and have come up with the
following.

"=sumproduct((APN!$" & [MyRngPer] & [chr(38)] & [chr(34)] & [chr(34)] &
"=$A"_ & [Nu] & [chr(38)] & [chr(34)] & [chr(34)] & ")*(APN!" & MyRngNm &
"=$C"_
& Nu1 & ")*(APN!" & MyRngAF & "))"

One of my colleagues said I should try placing the brackets [] around each
occurence of my variables- MyRngPer, Nu, Nu1, MyRngAF, and chr()-- as you can
see above.
So far it hasn't returned any compile errors but I've stumbled across
another issue I need to deal with, and will start another, distinct post for
that.



"Dave Peterson" wrote:
If it doesn't help the OP, maybe he could post back with what each of those
variables contains--real data that can be plugged into a test macro.

I don't understand what myRngPer is.

Is it a variable inside your code (and what does it contain) or is it a range
name that you created in excel (Insert|name|define)?

I don't understand why you're using &"" in your formula. Do you have a mixture
of cells that contain digits--but some are text and some are really numbers?

Instead of a series of inputboxes to ask for row numbers (and having to validate
all the possible errors), you could use application.inputbox and prompt the user
for a range--just point and select.

Then the next question becomes: Is the data always in columns E, F and C?

Dim myRng as range
set myrng = nothing
on error resume next
set myrng = application.inputbox(Prompt:="Select the first criteria range", _
type:=8)
on error goto 0
if myrng is nothing then
exit sub 'user hit cancel
end if

'and do you a prompt for the cell that contains the criteria for column F
comparison?



SteveDB1 wrote:

Can do Dave,

=sumproduct((APN!$E$5:$E$200&""=$A14&"")*(APN!$F$5 :$F$200=$C14)*(APN!$C$5:$C$200))

When the row, and column does not show the sheet name, it's the sheet on
which I have placed the sumproduct equation.
What I would LIKE to be able to do is to have a series of input boxes that
allow me to choose the variable source rows from the APN! worksheet. I.e., I
never know from one workbook to the next-- we have ~780 workbooks that are
regularly updated-- what the start row of the APN worksheet is going to be.
I've seen then as low as 3, and as high as 8.
I'd then like to be able to set the variable test (=$A14&"", or =$C14) row
location as well. The columns are always the same, but the start rows will
vary-- again, as low as 8, and as high as 19.

The first two arrays are my criteria, and the last of the 3 arrays is my
tally if it finds anything that returns a true from the first two arrays.

I've been tinkering as we post back and forth and have come up with the
following.

"=sumproduct((APN!$" & [MyRngPer] & [chr(38)] & [chr(34)] & [chr(34)] &
"=$A"_ & [Nu] & [chr(38)] & [chr(34)] & [chr(34)] & ")*(APN!" & MyRngNm &
"=$C"_
& Nu1 & ")*(APN!" & MyRngAF & "))"

One of my colleagues said I should try placing the brackets [] around each
occurence of my variables- MyRngPer, Nu, Nu1, MyRngAF, and chr()-- as you can
see above.
So far it hasn't returned any compile errors but I've stumbled across
another issue I need to deal with, and will start another, distinct post for
that.

"Dave Peterson" wrote:
If it doesn't help the OP, maybe he could post back with what each of those
variables contains--real data that can be plugged into a test macro.

--

Dave Peterson

Dave,
First, this is my first "full-scale" macro that I'm doing from scratch.
I've made various attempts in the past, but found that I understood far too
little to go where I wanted. As a result, I'd record actions that I wanted,
and then looked at the code to streamline what I could, because I'd do
actions in a somewhat disorderly manner. I've done about a couple of dozen or
so macros in that manner. Last week I started putting this macro together
based on some ideas I had and what I could understand from the Excel VBA 2007
Programmer's Reference manual from WROX.

I don't understand what myRngPer is.

Is it a variable inside your code (and what does it contain) or is it a range
name that you created in excel (Insert|name|define)?

MyRngPer is the name of the variable that I gave to select a range of data.
I did the following to code that.

dim MyRngPer as Range
MyRngPer = inputbox(Prompt:=".....",Title:=".......")

As for the large number of input boxes, I'd rather do it with a user's form.
However, my last attempt at a user form is still waiting for me to go back
and find out why it doesn't work the way I thought it would. I know it's
something I did wrong, I just haven't gone back to it yet to find out what I
did wrong. I had an interesting idea that had too many input boxes, like
this one does, so I wanted to try user forms.

Picking a range of data would be nice, automatically, if the workbooks we
have weren't so different in start, and end points.
As I said we have around 780 workbooks that we update regularly, and while
I've gone through about 50 to 100 with a manually entered version of what
I've shown you here, I'm tired of spending 5 minutes on each one, and wanted
to speed up the input process.


I don't understand why you're using &"" in your formula. Do you have a mixture
of cells that contain digits--but some are text and some are really numbers?


your statement on the &"" is correct. For reasons unknown to me, and work
done prior to my coming to work here, the columns where the &"" are used to
compare were given various data type formats. I tried using the sumproduct
equation without them for close to 4 months when I started finding one
situation where it would work great, and another where it wouldn't-- as you
can imagine it got really irritating fast. I wanted to help streamline the
process for other coworkers as well, and so it had to work under all
circumstance, regardless. Harley Grove, and another guy from Britain helped
me understand the benefit, and necessity of the &"". I think I've only found
one situation where it didn't work (out of thousands of lines, and perhaps a
100 workbooks; this includes linking different workbooks together with it),
and I was able to do something else that fixed it.

Instead of a series of inputboxes to ask for row numbers (and having to validate
all the possible errors), you could use application.inputbox and prompt the user
for a range--just point and select.



I just found the application.inputbox example on page 69 of the WROX
reference book. I'll use that instead of all the individual input boxes.
Thanks.

'and do you a prompt for the cell that contains the criteria for column F
comparison?

As to the last question, the
.....(APN!$F$5:$F$200 = $C14)
where $C14 is what the range on the APN worksheet is looking for.
So, yes, it'd be helpful to have a prompt to call it.

Then the next question becomes: Is the data always in columns E, F and C?

Yes, for one use of the sumproduct.
I'll have a second use where only the third column changes. But then it'll
always be column B.
So, on my primary use, Columns E, F, and C
on my secondary use, Columns E, F, and B.
Both of these uses are standard.

Once in a great while-- I think there are 5 to 10 workbooks out of the
780--that will have two columns of the data normally only in column C.

I do remember one workbook that will have 4 columns of the data normally in
column B.
So, for these few occurences where there is something that differs, I can do
them manually, if I haven't done them already. Two of the odd ones I was
using sumif because I wasn't aware of the sumproduct at the time. I know that
all of that is still in one file. In fact, now that I think about it, it was
that file that got me looking more on how to speed things up. I literally was
testing one line at a time to see if I had everything.

Please talk to me more about what you're doing here below.


Dim myRng as range
set myrng = nothing
on error resume next
set myrng = application.inputbox(Prompt:="Select the first criteria range", _
type:=8)
on error goto 0
if myrng is nothing then
exit sub 'user hit cancel
end if

The brackets are unnecessary, except maybe for forcing you to remove
extraneous ampersands. Remove them and the formula you've written this time
should finally work. The square brackets are a shortcut for the keyword
Evaluate(), which means they slow down execution.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"SteveDB1" wrote in message
...
Can do Dave,

=sumproduct((APN!$E$5:$E$200&""=$A14&"")*(APN!$F$5 :$F$200=$C14)*(APN!$C$5:$C$200))

When the row, and column does not show the sheet name, it's the sheet on
which I have placed the sumproduct equation.
What I would LIKE to be able to do is to have a series of input boxes that
allow me to choose the variable source rows from the APN! worksheet. I.e.,
I
never know from one workbook to the next-- we have ~780 workbooks that are
regularly updated-- what the start row of the APN worksheet is going to
be.
I've seen then as low as 3, and as high as 8.
I'd then like to be able to set the variable test (=$A14&"", or =$C14) row
location as well. The columns are always the same, but the start rows will
vary-- again, as low as 8, and as high as 19.

The first two arrays are my criteria, and the last of the 3 arrays is my
tally if it finds anything that returns a true from the first two arrays.

I've been tinkering as we post back and forth and have come up with the
following.

"=sumproduct((APN!$" & [MyRngPer] & [chr(38)] & [chr(34)] & [chr(34)] &
"=$A"_ & [Nu] & [chr(38)] & [chr(34)] & [chr(34)] & ")*(APN!" & MyRngNm &
"=$C"_
& Nu1 & ")*(APN!" & MyRngAF & "))"

One of my colleagues said I should try placing the brackets [] around each
occurence of my variables- MyRngPer, Nu, Nu1, MyRngAF, and chr()-- as you
can
see above.
So far it hasn't returned any compile errors but I've stumbled across
another issue I need to deal with, and will start another, distinct post
for
that.



"Dave Peterson" wrote:
If it doesn't help the OP, maybe he could post back with what each of
those
variables contains--real data that can be plugged into a test macro.