convert index and match formula in a macro for a big dynamic spreadsheet

[JEAN1972]

I have a table of taxation up to 7000 rows sheet Table_1.

Sheet Recon in same workbook requires columns H which will lookup in table_1 column C to extract taxation rates from column E to H and k.

Actually using the formula below .

Looking a macro to convert the formula below with their respective headers from column E to H and K to last data row as spreadsheet is dynamic with 15000 rows of data

Please Login or Register  to view this content.

[jeffreybrown]

Not sure I understand why you need a macro? Are you saying you need a macro to find the last row? You can just change your reference to full columns.

Example: 'Table _1'!$C$2:$C$110 to 'Table _1'!C:C

or lookup dynamic ranges

I think the full column reference might be all you need

[JEAN1972]

I am looking for a macro because other users who work on the workbook mess with formula ,thus it look simpler with a macro button

[jeffreybrown]

I am looking for a macro because other users who work on the workbook mess with formula

Lock the sheet and they can't mess with your formulas.

Anyway, try this...

Please Login or Register  to view this content.

Apply to other columns as necessary.

[jindon]

Try

Please Login or Register  to view this content.

[JEAN1972]

@Jindon & Jeffrey thank you both for solution provided .

Both solutions works as a charm

However @Jindon

Can you assist the meaning below in the code

Please Login or Register  to view this content.

[jindon]

Open method has 4 protocols.
ActiveConnection, CursorType, LockType & Option and only ActiveConnection can not be omitted.

In your case

Please Login or Register  to view this content.

should suffice.

See details...
https://www.w3schools.com/asp/prop_rs_cursortype.asp

HTH