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.