Using WorksheetFuntion to Match/Count Data in an Array (For Each Loop)


New Member
Aug 30, 2018
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)

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:

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Here is the data

1151617Bamm BammCC From10/6/201810/8/201839DP092023P020AKGE400MSAP01022.00
2151617Bamm BammCC From10/6/201810/8/201839DP092023P020AKGE400R000620T2.00
3151617Bamm BammCC From10/6/201810/8/201839DP092023P020AKGE400MSAP01022.00
4151617Bamm BammCC From10/6/201810/8/201839DP092023P020AKGE400R000620T2.00
5181920Mary PoppinsCC To10/8/201810/14/2018MB01040605J870AJW8H00QUALPHCT0.00
6181920Mary PoppinsCC To10/9/201810/14/2018MB01040605J870AJW8H00QUALPHCT4.50
7181920Mary PoppinsCC To10/10/201810/14/2018MB01040605J870AJW8H00QUALPHCT0.00
8181920Mary PoppinsCC To10/11/201810/14/2018MB01040605J870AJW8H00QUALPHCT0.00
9181920Mary PoppinsCORRECTED10/12/201810/14/2018MB01040605J870AJW8H00QUALPHCT0.00
10181920Mary PoppinsCC From10/12/201810/14/2018MB01040605J870AJW8H00QUALPHCT0.00
11181920Mary PoppinsCC To10/12/201810/14/2018MB01040605J870AJW8H00QUALPHCT1.00
12212223Wonder WomanCC To10/1/201810/7/2018DP12020103R030AJ21M00PHYS00000.50
13212223Wonder WomanCC To10/1/201810/7/2018DP12010203R030AJ1PP00000000000.50
14212223Wonder WomanCORRECTED10/1/201810/7/2018DP12020403R030AJ6DP00000000000.75
15212223Wonder WomanCC From10/1/201810/7/2018DP12020403R030AJ6DP0000000000(0.75)
16212223Wonder WomanCC To10/1/201810/7/2018DP12020403R030AJ6DP00000000000.00
17212223Wonder WomanCC To10/1/201810/7/2018YN01000003R030AXA330000053R010.50
18212223Wonder WomanCORRECTED10/1/201810/7/2018DP12020103R030AJ2DP00000000000.50
19212223Wonder WomanCC From10/1/201810/7/2018DP12020103R030AJ2DP0000000000(0.50)
20212223Wonder WomanCC To10/1/201810/7/2018DP12020103R030AJ2DP00000000001.25
21212223Wonder WomanCC To10/1/201810/7/2018YN01000003R030AXA330000053R040.75
22212223Wonder WomanCC To10/1/201810/7/2018YN01000003R030AXA330000053R070.75
23212223Wonder WomanCC To10/1/201810/7/2018YN01000003R030AXA330000053R034.75
24212223Wonder WomanCC To10/2/201810/7/2018DP12020103R030AJ21M00PHYS00000.50
25212223Wonder WomanCC To10/2/201810/7/2018DP12010203R030AJ1PP00000000000.50
26212223Wonder WomanCORRECTED10/2/201810/7/2018DP12020403R030AJ6DP00000000000.75
27212223Wonder WomanCC From10/2/201810/7/2018DP12020403R030AJ6DP0000000000(0.75)
28212223Wonder WomanCC To10/2/201810/7/2018DP12020403R030AJ6DP00000000000.00
29212223Wonder WomanCC To10/2/201810/7/2018YN01000003R030AXA330000053R010.75
30212223Wonder WomanCORRECTED10/2/201810/7/2018DP12020103R030AJ2DP00000000000.50
31212223Wonder WomanCC From10/2/201810/7/2018DP12020103R030AJ2DP0000000000(0.50)
32212223Wonder WomanCC To10/2/201810/7/2018DP12020103R030AJ2DP00000000001.25
33212223Wonder WomanCC To10/2/201810/7/2018YN01000003R030AXA330000053R040.75
34212223Wonder WomanCC To10/2/201810/7/2018YN01000003R030AXA330000053R070.75
35212223Wonder WomanCC To10/2/201810/7/2018YN01000003R030AXA330000053R034.50
36212223Wonder WomanCC To10/3/201810/7/2018DP12020103R030AJ21M00PHYS00000.50
37212223Wonder WomanCC To10/3/201810/7/2018DP12010203R030AJ1PP00000000000.50
38212223Wonder WomanCORRECTED10/3/201810/7/2018DP12020403R030AJ6DP00000000000.75
39212223Wonder WomanCC From10/3/201810/7/2018DP12020403R030AJ6DP0000000000(0.75)
40212223Wonder WomanCC To10/3/201810/7/2018DP12020403R030AJ6DP00000000000.00
41212223Wonder WomanCC To10/3/201810/7/2018YN01000003R030AXA330000053R010.75
42212223Wonder WomanCORRECTED10/3/201810/7/2018DP12020103R030AJ2DP00000000000.50
43212223Wonder WomanCC From10/3/201810/7/2018DP12020103R030AJ2DP0000000000(0.50)
44212223Wonder WomanCC To10/3/201810/7/2018DP12020103R030AJ2DP00000000001.25
45212223Wonder WomanCC To10/3/201810/7/2018YN01000003R030AXA330000053R040.50
46212223Wonder WomanCC To10/3/201810/7/2018YN01000003R030AXA330000053R070.75
47212223Wonder WomanCC To10/3/201810/7/2018YN01000003R030AXA330000053R034.75

<colgroup><col span="2"><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back