Converting single formula to VBA to cover output area

Hi,

I have a sheet called "Consolidated data" in an Excel workbook. The sheet has prefilled information in A7:E698 (which can be extended or shortened downwards/vertically depending on raw data) and F1:CVM6 (which can be extended or shortened across/horizontally depending on raw data).

I have the following formula in cell F7, which is copied across F7:CVM698, but which needs to cover a larger or smaller area depending on the prefilled information described above:

=IFERROR(INDEX(INDIRECT("SAP"&"[["&F$6&"]:["&F$6&"]]"),MATCH('Consolidated data'!$B7&'Consolidated data'!F$1&'Consolidated data'!F$2&'Consolidated data'!F$3&'Consolidated data'!F$4&'Consolidated data'!F$5,SAP[[Batch]:[Batch]]&SAP[[Operation short text]:[Operation short text]]&SAP[[Step Text]:[Step Text]]&SAP[[MIP Text]:[MIP Text]]&SAP[[MIP]:[MIP]]&SAP[[Int2]:[Int2]],0)),"")

Formula:

Please Login or Register  to view this content.

I need a VBA version of the formula above, which calculates the results required for the area defined by the abovementioned prefilled information, currently covering F7:CVM698.

I'd appreciate if anyone could help me with the code.

Best regards,
Marbleking

Given that you are using Office 365, I'd have thought you could use Structured Tables OR the new dynamic functions like SORT, FILTER, UNIQUE.

Note that you can't use the new functions in a Structured Table.

Please see the yellow banner at the top of the page.

Thread closed in favour of new one opened in the CS section: https://www.excelforum.com/commercia...tput-area.html