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