Converting formula to VBA

[manc]

Hi Demigods,

I have the following formula as an example in cell F1 of my sheet:

=IFERROR(IF((VLOOKUP(D1,SSCC_bin_H!A:A,1,FALSE))=SSCC_counter_H!D1,"A",""),"")

This formula is in every single row of column F to the bottom of the sheet - in this case row 205102.

As you can imagine, it slows down the functionality of the sheet no end, and this type of formula (or a variation of) is repeated in other columns too!

What i'd really like to do, as per google sheets, is put an array formula in F1 and it populates the whole column. Failing that, can someone help me with VBA code to achieve the same?

Any help much appreciated.
Best regards
manc

 

[StephenCrump]

One immediate observation is that you won't necessarily need to use VLOOKUP in every row:

=IF(D1=SSCC_counter_H!D1,IF(ISNUMBER(MATCH(D1,SSCC_bin_H!A:A,)),"A",""),"")

We can also write much faster VLOOKUP (or MATCH) constructions if we sort SSCC_bin_H!A:A.

A VBA solution may be appropriate.

But I can't imagine why you need hundreds of thousands of lookup formulas across multiple columns in any event. What are you doing with your data?