formular1c1 questions

[oaishm]

This formula r1c1 stuff is like voodoo. There has to be documentation of it soemwhere. Anyway, here's my question:

Why doesn't the computer like this?
Worksheets("QOutput").Cells(x, y).FormulaR1C1 = "=SUMIF('R'!c[5]:c[5],QOutput!r[x]c[1]&'Q','R'!c[y+11]:c[y+11])"

Since the only way I can think of passing two parameters through the sumif statement is by concatenating them, I do that in the second parameter here: QOutput![r[x]c[1]&'Q'

I'd like the output in the cell to be:
=sumif(R!e:e,1Q,R!l:l)

where
x = 13
y=2
QOutput!a13.value = 1

Thanks for the help

 

[Scott Huish]

What is 1Q? Named ranges can't start with a number.

Do you actually need R1C1? You can put A1 style formula in cells with VBA.

Are you running this through a loop? What range are you putting the formulas in? What is the first formula?

 

[Scott Huish]

I'm guessing this is what you want:
Worksheets("QOutput").Cells(x, y).FormulaR1C1 = "=SUMIF('R'!C5,'QOutput'!R" & x & "C1,'R'!C" & y + 11 & ")"

 

[oaishm]

Sorry, that didn't work. "Q1" is a text I have in a cell that tells me to sum a number in another cell in the same row. My fundamental problem is I don't know how to build a formula when I need quotes in my formula.

 

[Scott Huish]

So do you want the text Q1 hardcoded into the formula or do you want a cell reference there?

If you want Q1 hardcoded, try this:

Worksheets("QOutput").Cells(x, y).FormulaR1C1 = "=SUMIF('R'!C5,""Q1"",'R'!C" & y + 11 & ")"

 

[oaishm]

I got to the bottom of it. It's really inexplicable

This works

z = 13
Worksheets("QOutput").Cells(x + 12, y).FormulaR1C1 = "=SUMIF('R'!c5,rc[-1]&""Q"",'R'!c[13])"

This doesn't work

z = 13
Worksheets("QOutput").Cells(x + 12, y).FormulaR1C1 = "=SUMIF('R'!c5,rc[-1]&""Q"",'R'!c[z])"

I can't imagine why

 

[timorrill]

You would need to separate z from the rest of the string:

Worksheets("QOutput").Cells(x + 12, y).FormulaR1C1 = "=SUMIF('R'!c5,rc[-1]&""Q"",'R'!c[" & z & "])"