Converting Dynamic Array Formula to VBA solution

[Luke777]

Hi all,

I was recently provided with a fantastic dynamic array formula that handles the my data exactly as I want. Unfortunately, with the size of my data set, it takes far too long to calculate and recalculates unnecessarily. I would like to know if its possible to convert the formula to VBA, which I believe will speed things up if not at least avoid recalculation at inconvenient times.

C4	=XLOOKUP(C1#&ROUND(A4:A10,5),Sheet1!A1:A20&ROUND(Sheet1!B1:B20,5),Sheet1!C1:C20,"")

The formula exists in C4 on Sheet2. Sheet1 is the data source, the first 20 rows of which looks like the following

286	06:30:00	46
214	06:30:00	46
1535	06:30:00	46
1580	06:30:00	46
1307	06:30:00	46
1029	06:30:00	53
57	06:30:00	23
286	06:45:00	12
214	06:45:00	12
1535	06:45:00	12
1580	06:45:00	12
1307	06:45:00	12
932	07:30:00	46
538	07:30:00	46
932	07:45:00	12
542	07:45:00	46
538	07:45:00	12
542	08:00:00	12
241	08:00:00	46
989	08:00:00	23

The axes on Sheet2 are the uniques of sheet1 A:A transposed to Sheet2 row 1 (starting at C) & date/time in 15 minute intervals starting at 06:30 A4:A148

Basically the formula provided does the work required, just too slowly and without enough control over when it functions. I also need to edit the results at a later date e.g run a script to change a specific number to a specific string in D44 which I believe would be overwritten if the sheet recalculated.

Thanks all