MaxIf Across Discontinuous Range - VBA Or Formula

I want to create VBA code, or a formula that achieves MaxIf across a Discontinuous Range.

I already have the basic Max If array formula which works in another place:

{=MAX(IF(EditAsset_MaintenanceType="Scheduled Maintenance",EditAsset_MaintenanceDateUndertaken))}

Where each named range is a column of 20 continuous cells.

This actually sits within a larger formula {=IF(MAX(IF(EditAsset_MaintenanceType="Scheduled Maintenance",EditAsset_MaintenanceDateUndertaken))=0,EditAsset_DatePurchased+(EditAsset_MaintenanceCycle*30.4167),MAX(IF(EditAsset_M aintenanceType="Scheduled Maintenance",EditAsset_MaintenanceDateUndertaken))+(EditAsset_MaintenanceCycle*30.4167))}

Now I need to achieve a similar thing, but in a master list of all the data which necessitates a different layout so the data can't be in 2 columns, and can't be continuous. So:

Max the dates in cell BE7, BI7, BM7, BQ7, BU7, BY7, CC7, CG7, CK7, CO7, CS7, CW7, DA7, DE7, DI7, DM7, DQ7, DU7, DY7, EC7...

...IF the value of the corresponding cell two columns to the left of each is "Scheduled Maintenance",

e.g. cells BC7, BG7, BK7, BO7, BS7, BW7, CA7, CE7, CI7, CM7, CQ7, CU7, CY7, DC7, DG7, DK7, DO7, DS7, DW7, EA7

I tried creating 2 named ranges (one for the cells in each bullet point above) and using that in the array formula, but that didn't work and as this formula needs to go down 8,000 rows it would require 16,000 named ranges which isn't really practical.

Ideally I think I would perform this calculation in VBA and just output the answer to the relevant cell. [The VBA code will be triggered via a Worksheet_Change event when one of the date cells, or the 'maintenance cycle' cell elsewhere is changed].

I think this could be done by taking the values from the cells above into two arrays, and then 'max if' those arrays within VB somehow - but I don't know how to create the arrays or then perform 'max if' within VBA.

Alternatively a formula would suffice - I could insert this formula into the output cell via the VBA Worksheet_Change event when an input cell (date or maintenance cycle) is changed, and then have the same VBA code copy this cell and paste the value - can't have up to 8,000 array formulas live in my sheet for performance reasons! But I don't think this will be easily possible via a formula and VBA would be cleaner.