Looping through an array + filter based on array values

[yeldar13]

Hi everyone,

I am totally new to VB and am trying to put together a macro. In short, I want it to select a range, store the values as an array (employee names). Then I want to loop through the values in the array. For each iteration, I want to go into a different sheet, filter it by each array value (each employee name), count the numbers of rows that show up in the filter result (represents holidays taken to date), make another array containing these values and then print this new array in the column beside the range where the original array was extracted so I have the employee name with holidays taken to date beside it.

I hope this makes sense!!

I really need some help with this. This is what I have come up with so far and it's not working but I hope you can help me get onto the right track!

Sub Test()


      Sheets("Employees").Select
      
      Dim myarray As Variant

      myarray = Range("B14:B23").Value

      Dim newArray As Variant

      For i = 1 To UBound(myarray)

       
        Sheets("Holidays").Select
        
        ActiveSheet.Range("$A$1:$N$302").AutoFilter Field:=12, Criteria1:= _
        "=myarray", Operator:=xlAnd

        Range("R307").Select
        
        ActiveCell.FormulaR1C1 = "=SUBTOTAL(3,R[-160]C[-5]:R[-157]C[-5])" 'This is where I am stuck

        Range("R307") = newArray '???
        

      Next

        Sheets("Employees").Select
        
        Range("C14:D23").Value = newArray
        
End Sub

[monarchmk]

Let me try from some different angle...

Employee names are in cells B14 to B23
In sheet Holidays, Employees names are in column L (12 column) mentioned in Autofilter in row 18th.
So you need to count how many times Employee name is listed in columns 12 of sheet Holidays?
If this is correct you do not need arrays..
In every cell from C14 to C23 put this formula:

=COUNTIF(Holidays!L2:L523;B14)

This is sample for C14, in C15 change B14 to B15 etc...
range in holidays sheet is where i think that names are, so you can change this rang.
P.S. After some readin of your code i noticed that you try to make subtotal from column 13 (M column). If you need SUM instead of COUNT of cells then use this formula

=SUMIF(Holidays!L2:L523;B14")

Remember that this is formula for C14, you must change B14 to B15 for next row...
Everything is same as with countIF except that this formula makes SUM
and if you like to use different range like sum of M but criteria from L cells than use this formula

=SUMIFS(Holidays!M2:M523;Holidays!L2:L523;B14)

This is a bit more complicated but below you can find explanation. This sums all numbers in M columns, only from rows that have value equal to cell B14 in column L (from 2 to 523 row)
CountIF
COUNIF(range, criteria) - counts occurrences in cell range based on criteria
SUMIF
SUMIF(range,criteria) - makes sum of cells based on criteria
SUMIFS
SUMIFS(range, criteria range, criteria) - makes sum from range with rows that in criteria range have values equal to criteria.
Hope this helps you

P.S.
I try your code... and i'm wondering...
Range("R307").Select 'You select R columns, but later you calculate with M column?
ActiveCell.FormulaR1C1 = "=SUBTOTAL(3,R[-160]C[-5]:R[-157]C[-5])"
And Here you say (from current position) offset from 160rows UP, 5 columns LEFT TO 157rows up, 5 columns left
which means ROW 307-160=147 to 307-157=150 column M (R-5=M column).
and you making subtotal from M147:M150? I think subtotal should be
=SUBTOTAL(3,R[-305]C[-5]:R[-1]C[-5] 'If you are insisting on column R

Well, lots of explanation for 1 row formula, but if there is something that need more clarify do not hesitate to ask. And try to avoid complications in code. If you can make program with one row of code, just do it.