CODEWITHSUNDEEP
vbaexcelexcel-formula
Manish
Manish

Reputation: 213

Excel VBA Formula with Dynamic Range is not working

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.

Upvotes: 1

Views: 540

Answers (1)

R3uK
R3uK

Reputation: 14547

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) & ")"

Upvotes: 2

Related Questions