Excel VBA to count records based on matching dates criteria

[aaleem]

Hi,

currently, I have a sheet of data that contains thousands of records, I would like to match each record and provide the summarized results.
the criteria will be

if the batch date = payment date then it should count the no. of records as Current Dated
if the batch date > payment date then it should count the no. of records as Back Dated
If the batch date < payment date then it should count the no. of records as Post Dated
A sample sheet of data is attached here for reference.

Im currently using the if formula, howe we can achieve the same result in VBA, as the no. of records are voluminous using formula is making the excel slow and increasing its file size.

thanks
Aleem

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	Payment ID	Match Doc Ty	Payment/ Item	Payee Number	Bank Acct-G/L	Check/ Itm Date	Void Date	Batch Number	Bth Ty	Batch Date	Payment Amount	Status
2	1	PN	52667107	1278624	02539003	16/05/2021		52667073	M	16/05/2021	(382,784.14)	Current Dated
3	2	PN	52667118	1261005	02539003	15/05/2021		52667074	M	16/05/2021	(516,758.59)	Back Dated
4	3	PN	52667129	1276374	02539003	16/05/2021		52667075	M	16/05/2021	(697,624.10)	Current Dated
5	4	PN	52667140	1248451	00122098	16/05/2021		52667076	M	16/05/2021	(941,792.53)	Current Dated
6	5	PN	52667151	1276283	00122098	16/05/2021		52667077	M	16/05/2021	(1,271,419.92)	Current Dated
7	6	PN	52667162	1259758	00122098	31/05/2021		52667078	M	16/05/2021	(1,716,416.89)	Future Dated
8	7	PN	52667173	1221375	00122098	18/05/2021		52667079	M	16/05/2021	(2,317,162.81)	Future Dated
9	8	PN	52667184	106907	00122098	16/05/2021		52667080	M	16/05/2021	(3,128,169.79)	Current Dated
10	9	PN	52667195	1269342	00122098	19/05/2021		52667081	M	16/05/2021	(4,223,029.22)	Future Dated
11	10	PN	52667206	1271214	00122098	20/05/2021		52667082	M	16/05/2021	(5,701,089.44)	Future Dated
12
13
14					Results should be
15						Current Dated Records	5	which is = 16/05/2021
16						Back Dated	1	which is < 16/05/2021
17						Future Dated	4	> 16/05/2021
18					Formula used			IF(J2=F2,"Current Dated",IF(J2>F2,"Back Dated","Future Dated"))
Sheet1

Cell Formulas
Range	Formula
L2:L11	L2	=IF(J2=F2,"Current Dated",IF(J2>F2,"Back Dated","Future Dated"))

 

[Peter_SSs]

Any insight into the three questions above would be welcomed!

Hi @kennypete
I'm not sure that I can add a lot in answering your questions. I have no formal training or knowledge about vba. Almost everything I know about it I picked up in this forum. In particular, in relation to this code I originally got the concept of loading the array that way from this thread, where you might find some of the discussion by pgc01 and Rick Rothstein helpful, including the fact that during loading an array using Application.Index you can alter the order of the columns or rows.

presuming your Worksheet is Sheet1, of course

No, my code was simply assuming the sheet with the data is the active sheet since unqualified range references in a general module will refer to the active sheet.

 

[kennypete]

I'm not sure that I can add a lot in answering your questions. I have no formal training or knowledge about vba. Almost everything I know about it I picked up in this forum. In particular, in relation to this code I originally got the concept of loading the array that way from this thread, where you might find some of the discussion by pgc01 and Rick Rothstein helpful, including the fact that during loading an array using Application.Index you can alter the order of the columns or rows.

Thanks Peter, that's all good.

In terms of Q1, Application.WorksheetFunction.Index versus Application.Index, yes the link referenced in the thread you quoted more-or-less explains it: The WorksheetFunction Method – Daily Dose of Excel

In terms of Q2, "Cells", I did find this: "When you apply the Cells property to a worksheet without specifying an index number, the method returns a Range object that represents all the cells on the worksheet - "Refer to All the Cells on the Worksheet so that answers that question. So, Cells could be replaced with something like Range("A:J") in this instance and the result would be the same. Luckily the Cells call when within that Index function does not exceed memory anyway when limited by the rows and columns details.

• Incidental: For some light entertainment I found that Dim x: x = Range("A:EZ") sees Excel memory usage jump to nearly 4GB and Dim x: x = Range("A:FZ") gives a Runtime error 7 out of memory. Now I have 32GB RAM so clearly it's not even close to hitting that and Out of memory (Error 7) does not suggest a 4GB limit. Perhaps it's: Large Address Aware capability change for Excel - Office - noting, "the available memory for the Excel process is automatically doubled from 2 GB to 4 GB", though I have 64-bit Office 365 so perhaps it was not even fixed for the 64-bit version.

Finally, as for Q3, Two and one dimensional row and column args: from testing it, the reason for this is that row produces a 2D array whereas column produces 1D. Compare:

Dim r: r = [row(2:12)]
Dim c: c = [column(F:J)]

If row did not behave this way I guess using Array() could work for it too rather than having to use Evaluate/Row, which does seem the quickest way of populating the 2D array (versus using a cluncky Dim r(): ReDim Preserve r(1 To n, 1 To 1): r(1, 1) = 2: r(2, 1) = 3, perhaps with a loop, to populate the 2D array for the rows, which does work).

Anyway, 'twas an interesting one, thanks.

 

[Peter_SSs]

So, Cells could be replaced with something like Range("A:J") in this instance and the result would be the same.

It could be, but Rick's observation in that thread was that there seemed to be no detriment to using Cells - & it is simpler to type.