Excel VBA Formula with Dynamic Range is not working

1
votes

I have following formula in Excel, where I applying countif with dynamic range. When I apply this formula in macro, this is not giving value instead single quote (') is covering the range. 

Cells(XRow, 3).Formula = "=COUNTIF( " & Range(Cells(6, 5), Cells(YRow - 1, 5)).Address(0, 0) & "  ,RC[-1])"

This is what I get in cell

=COUNTIF( 'E6':'E212',B6)

I am not sure why single quote occupy the cell 'E6', If I remove ' manually from cell, it works. Any help would be much appreciated.

1
vbaexcelexcel-formula
I'm going to guess it's because you are attempting to use both A1 reference style and R1C1 in the same formula. - Dave

1 Answers

2
votes

You are mixing styles of references xlA1 and xlR1C1 :

Use this :

Dim Rg As Range
Set Rg = Cells(XRow, 3)
Rg.Formula = "=COUNTIF(" & Range(Cells(6, 5), Cells(YRow - 1, 5)).Address(0, 0) & "," & Rg.Offset(0, -1).Address(0, 0) & ")"