Having Problems understanding R1C1.

[Guzzlr]

Hello
I have from the recoreder:

Set BurnDown = Sheets("BurnDown")
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(ECD!C[5],BurnDown!RC[-1])"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C81"), Type:=xlFillDefault
    Range("C2:C81").Select

What would this code be with out all the "select" stuff and not using R1C1 formatting?
Thanks for the help

 

[Fluff]

Try

Sheets("BurnDown").Range("C2:C81").FormulaR1C1 = "=COUNTIF(ECD!H:H,B2)"

The easiest way to find out what the formula is in A1 notation, is to look at the formula in the cell.

 

[Joe4]

Its is pretty much the same as waht fluff told you last week here: R1C1 ConuntA formula
Just apply it to multiple cells instead of one cell.

 

[joeu2004]

Sheets("BurnDown").Range("C2:C81").FormulaR1C1 = "=COUNTIF(ECD!H:H,B2)"

I believe that should be:

Range("C2:C81").Formula = "=COUNTIF(ECD!H:H, 'BurnDown'!B2)"
or
ActiveSheet.Range("C2:C81").Formula = "=COUNTIF(ECD!H:H, 'BurnDown'!B2)"

1. In this code snippet, there is no BurnDown.Select. (Contrast with the code in the discussion cited by Joe4.)
Perhaps an unintended omission here. But without it, Range("C2:C18") is in ActiveSheet, which might not be BurnDown.

2. With A1 references, we must use Formula. FormulaR1C1 raises an error.

3. We must reference the BurnDown worksheet explicitly in the COUNTIF formula.
Otherwise, B2 refers to ActiveSheet.B2, which might not be BurnDown.B2, as noted in #1.

4. I use 'BurnDown'!B2 instead of simply BurnDown!B2 to demonstrate the necessary syntax
(single quotes) if the sheet name had included non-alphanumeric characters, notably spaces.
In this particular example, BurnDown!B2 (without single quotes) would work, too.

PS.... Arguably, one difference between this form and using AutoFill might be the format of the cells. I have not bothered to check, though. Someone else might. In other words, what if the format of C2 is different from C3:C18?

 

[Guzzlr]

Its is pretty much the same as waht fluff told you last week here: R1C1 ConuntA formula
Just apply it to multiple cells instead of one cell.

So i tried playing around with it for some time, and it kept error on me, no matter what different
scenarios I tried...so in a fit of rage, I asked you guys...

 

[Guzzlr]

The easiest way to find out what the formula is in A1 notation, is to look at the formula in the cell.

Never crossed my mind to do this
Thanks for the help

 

[Fluff]

Glad we could help & thanks for the feedback.