Bug when inserting formula with VBA

[blista99]

Hy everyone

I have got this weird bug, which I can't understand.
With the following code in VBA I try to insert a formula:

    For Each Cell In Range("A28:A102")
        If Cell.Value <> "" Then
            Cell.Offset(0, 1).FormulaR1C1 = "=COUNTIF(Januar_Data!J:J,RC[-1])"
        End If
    Next

But the result in the cells is for example:
=COUNTIF(Januar_Data!J:(J),A28)

The "red" brackets just appear out of nowhere and corrupt the formula which then will not work.

Any ideas, what could be the problem?

Thanks for your suggestions.

 

[RoryA]

You are mixing reference styles. Use:

"=COUNTIF(Januar_Data!C10,RC[-1])"

for the R1C1 version

 

[blista99]

I don't see the difference? Do you mean I cannot use the "J:J"-Part? or
only write ....Formula="=COUNTIF(Januar_Data!J:J,RC[-1])" <- Endresult is same as above

Because if I type the formula manually into the cell, it works.

 

[RoryA]

You're mixing A1 style references (the J:J part) with R1C1 style (RC[-1]) and you can't do that - use one or the other. So either:

Cell.Offset(0, 1).FormulaR1C1 = "=COUNTIF(Januar_Data!C10,RC[-1])"

(C10 = column 10, which is column J)

or use:

Cell.Offset(0, 1).Formula = "=COUNTIF(Januar_Data!J:J," & cell.address(0, 0) & ")"

 

[blista99]

Wow. I feel stupid. I think I was into VBA for too long today....... C10...of course Collumn10. I first thought about the cell "C10".

Yeah. Now it works. Thanks a bunch RoryA.