Here is my code. I am about 4 months new using VBA and I am struggling with this code. I have the array working well but I can't seem to get my Loop functioning. I want it to count by Employee Id, by Date, the number of of CC From(s),CC To(s),CORRECTION string data types for that employee by date. The three string data types are in cell D4,D3,D2. Can you help me understand what I may be doing wrong here?
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 B (2) Employee Id
Dim Eid As Long
Eid = vs.CellS(i, 2).Value
Dim Eid2 As Long
Eid2 = vs.CellS(i + 1, 2).Value
'Column E (5) 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 (12)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
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 B (2) Employee Id
Dim Eid As Long
Eid = vs.CellS(i, 2).Value
Dim Eid2 As Long
Eid2 = vs.CellS(i + 1, 2).Value
'Column E (5) 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 (12)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
Last edited:
