Hello - So, I am about 4 months into learning VBA and am struggling in doing a loop that returns a count. The attached file provides two tabs, "Sampled Data" and, "Code." Here is the code. Can someone help me?
I hope to hear from you soon.
Thanks, Cora
Sub CountEidDaMem()
Dim vs As Worksheet
Set vs = ThisWorkbook.Sheets("SAMPLED DATA")
Dim erow As Long
erow = vs.CellS(Rows.Count, 1).End(xlUp).Row
Dim i As Long
i = 8
'Column A Employee Id
Dim Eid As Long
Eid = vs.CellS(i, 2).Value
Dim Eid2 As Long
Eid2 = vs.CellS(i + 1, 2).Value
'Column D Expense Item Date
Dim ExpD As Long
ExpD = vs.CellS(i, 5).Value
Dim ExpD2 As Long
ExpD2 = vs.CellS(i + 1, 5).Value
'Column L Counts
Dim CMem As Long
CMem = vs.CellS(i, 12).Value
Dim CMem2 As Long
CMem2 = vs.CellS(i + 1, 12).Value
Dim CMem3 As Long
CMem3 = vs.CellS(i + 2, 12).Value
Dim MyArray() As Variant
Dim Dimension1 As Long
Dimension1 = Range("B8", Range("B7").End(xlDown)).CellS.Count - 1
Dim Dimension2 As Long
Dimension2 = Range("B7", Range("B7").End(xlToRight)).CellS.Count - 7
ReDim MyArray(0 To Dimension1, 0 To Dimension2)
'Loop
For Dimension1 = LBound(MyArray, 1) To UBound(MyArray, 1)
For Dimension2 = LBound(MyArray, 2) To UBound(MyArray, 2)
MyArray(Dimension1, Dimension2) = Range("B8").Offset(Dimension1, Dimension2).Value
Next Dimension2
Next Dimension1
Dim Arr As Variant
Dim x As Long
Dim y As Date
For Each Arr In MyArray
x = Application.WorksheetFunction.Match(MyArray, Eid, 0)
y = Application.WorksheetFunction.Match(MyArray, ExpD, 0)
Arr.CMem.Value = Data.Count("x:y", Range("D4").Value)
Arr.CMem2.Value = Data.Count("x:y", Range("D3").Value)
Arr.CMem3.Value = Application.WorksheetFunction.Count("x:y", Range("D2").Value)
CountEid = CountEid + 1
Next Arr
End Sub