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.