PDA

View Full Version : [SOLVED] Countif function on varying ranges



Uthman
06-25-2018, 01:22 AM
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
06-26-2018, 04:39 AM
welcome to the forum.

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

Uthman
06-27-2018, 10:25 PM
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