DYNAMIC RANGE FOR CRITERIA in COUNTIF Function in VBA

[VBAEnjoi]

Want to copy COUNTIF Formula in a Column with Dynamic Row length
Unable to have the reference to Criteria dynamic, G4 in this case (highlighted in red,bold below).
When it's copied to next row it continues to stay as G4

Dim lr As Long
Dim strAd As String
Dim strFm As String




With ActiveSheet
lr = .Cells(.Rows.Count, "G").End(xlUp).Row
strAd = .Range("E4", .Cells(lr, 5)).Address(0, 0)
strFm = "=COUNTIF(" & strAd & ", G4 )"


End With

 

[Fluff]

Hi & welcome to MrExcel.
How are you putting the formula into the sheet?

 

[VBAEnjoi]

Currently through VBA code

 

[Fluff]

Can you please post the code?
Also when posting code please use code tags, the # icon in the reply window.

 

[VBAEnjoi]

Can you please post the code?
Also when posting code please use code tags, the # icon in the reply window.

###

Dim lr As Long
Dim strAd As String
Dim strFm As String

With ActiveSheet
lr = .Cells(.Rows.Count, "G").End(xlUp).Row
strAd = .Range("E4", .Cells(lr, 5)).Address(0, 0)
strFm = "=COUNTIF(" & strAd & ", G4 )"


End With

Range("H4").Formula = strFm




Range("H5").Formula = strFm

 

[Fluff]

Try

Dim lr As Long
Dim strAd As String
Dim strFm As String

With ActiveSheet
lr = .Cells(.Rows.Count, "G").End(xlUp).Row
strAd = .Range("E4", .Cells(lr, 5)).Address
strFm = "=COUNTIF(" & strAd & ", G4 )"


End With

Range("H4:H5").Formula = strFm

 

[VBAEnjoi]

Hey MrExcel MVP

That worked. Many thanks for the prompt revert

Could you explain what the Address expression does
And how can I make the ranges in the array variables instead of the current fixed
strAd = .Range("E4", .Cells(lr, 5)).Address

 

[Fluff]

What variables do you want to use?

 

[VBAEnjoi]

What variables do you want to use?

What I meant was that how could I make E4 as variable instead of constant $E$4
and last row too accordingly.

 

[Fluff]

If you want them as relative addresses use

.Address(0,0)