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

 

[VBAEnjoi]

If you want them as relative addresses use

.Address(0,0)

.Address(0,0)
Using Address(0,0) only impacts G4 in the formula.
The $E$4 doesn't change. Code below

###
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]

To use code tags, do not use the # on the keyboard, but the # ICON in the reply window, just above where you type. Simply click the icon & then paste your code between the tags that appear.
The code you have just posted does not change the G4 reference, only the E4:E? reference.

Col H below is without the (0,0) & col I is withit

Excel 2013/2016
	H	I
4	0	0
5	0	0
asc

Cell Formulas
Range	Formula
H4	=COUNTIF($E$4:$E$8, G4 )
H5	=COUNTIF($E$4:$E$8, G5 )
I4	=COUNTIF(E4:E8, G4 )
I5	=COUNTIF(E5:E9, G5 )

 

[VBAEnjoi]

Got it.

on top of the message response.

Will check your response and revert.

Thanks for the prompt responses.

[/CODE]

To use code tags, do not use the # on the keyboard, but the # ICON in the reply window, just above where you type. Simply click the icon & then paste your code between the tags that appear.
The code you have just posted does not change the G4 reference, only the E4:E? reference.

Col H below is without the (0,0) & col I is withit

Excel 2013/2016

	H	I
4	0	0
5	0	0

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

asc​

Worksheet Formulas Cell Formula H4 =COUNTIF($E$4:$E$8, G4 ) I4 =COUNTIF(E4:E8, G4 ) H5 =COUNTIF($E$4:$E$8, G5 ) I5 =COUNTIF(E5:E9, G5 ) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>