Convert array formula into vba code

Hi,
I am currently using a formula to calculate a result and then my macro does further processing on the data.

The problem is that this formula needs to be entered into a very large number of rows (~5000 rows). This makes it extremely slow and many times when the rows are more than ~6500, the formula fails to function. (as the formula also points to a range in another sheet).

Is there some way by which I can calculate the same result from within VBA and then continue processing the data as usual?

the formula is as below

=SUM(--ISNUMBER(MATCH('[Book1.xls]Input'!$E$2:$E$" & LastRow2 & ",A2:CZ2, 0)))