R1C1 vs A1: Add to column "A + 1"?

[beeawwb]

Hi all,

Just wondering. Is it possible to "add" to a letter, in the way you would with R1C1 style formulas?

I've always worked with LetterNumber style formulas, but I'm trying to add another iteration to a formula and while I can add to the row, I can't increase the column. I mean, you can't exactly say "Q+1" ... or can you?

Edit: I should mention, the formula I'm currently working with is...

.FormulaArray = "=SUM((Data!P3:P" & lastrow & "=A2)*(Data!Q3:Q" & lastrow & ">0))"

...I'm trying to make it so that on the next loop through (I'm copying the formula into multiple columns) Q3:QLastRow will be R3:RLastRow, and so on until the Do...Until loop closes. (The loop uses "loopcount" as an interation counter, so effectively it would be Q + "loopcount" iterations)

Thanks in advance,
Bob

[lecxe]

Hi Bob

Please notice that the vba help on the FormulaArray property is very clear: You should NOT use A1 notation (check it).

From the help:

If you use this property to enter an array formula, the formula must use the R1C1 reference style, not the A1 reference style

Remark: If you use the A1 notation it may also work sometimes, however other times you'll get errors.

[rylo]

Bob

You can convert the cell reference to an alpha.

Column Q is 17. So if your loop counter was 3, then you want to get the alpha representation of column 20.

alphacoll = WorksheetFunction.Substitute(Cells(1, 17 + loopcounter).Address(rowabsolute:=False, columnabsolute:=False), 1, "")

HTH

rylo

[beeawwb]

Hi Rylo,

Thanks for the formula, I knew there had to be some way to do it. However, after lecxe's post, I've started thinking I really should just bite the bullet and start teaching myself to think in R1C1.

I've rewritten my formulas to be R1C1 based, and they work great. Just took a bit of extra thought for my brain to say "Ok, so the brackets go here..."

Sheets("Summary").Cells(2, loopcounter + 1).FormulaArray = "=SUM((Data!R3C16:R" & lastrow & "C16=R2C1)*(Data!R3C" & 16 + loopcounter & ":R" & lastrow & "C" & 16 + loopcounter & ">0))"

Thanks again!

Bob