Countif function on varying ranges

[Uthman]

I need to fill the number of times a member appears in the data at the end of the table, However the sizes of the tables varry
How can i have the countif function work for the varying ranges
Below is the code i am currently using

'Visits on member sheet
Sheets("Members").Select

'Find last column
Set fourthcell = Cells(2, 2)
MemLastcol5 = Cells(fourthcell.Row, Columns.Count).End(xlToLeft).Column
MemLastrow5 = Cells(Rows.Count, fourthcell.Column).End(xlUp).Row

Range(Cells(3, MemLastcol5), Cells(MemLastrow5 - 1, MemLastcol5)).Select

Selection.FormulaR1C1 = _
"=+COUNTIF(Visits!R2C8:R69C8,Members!RC[-5]"

The member number column is sometimes 5 cells back or 6,3 maybe 2 so i need to make the countif function to varry to accomodate that

Any help rendered will be appreciated


UTHMAN

[mancubus]

welcome to the forum.

post your workbook pls. (#2 in my forum signature)

[Uthman]

welcome to the forum.

post your workbook pls. (#2 in my forum signature)

I was able to get a solution as below

We just use a loop to ensure it works just right
Dim op_id As String
For i = 3 To MemLastrow5

Op_id = Sheets("Members").Cells(i, 2)
Sheets("Members").Cells(i, MemLastcol5) = _
"=COUNTIF('Visits'!H:H, """ & Op_id & """)"

Next i