VBA to add index/match with references to currently active sheet rather than absolute reference

[Bert_Withpeterson]

Hi everybody,

I have the below index/match formula which works in looking up values from worksheet2 that are on worksheet1 and returning a final match. Problem is I'm looking to add this to some VBA which will create a large number of worksheets, so I need the formula to calculate based on, for example worksheet3 when on worksheet3, worksheet 4 when on worksheet4 etc. Can anyone help with amending the below so that where it says 'worksheet2' it actually points to whatever the current worksheet is?

Also can anyone please advise how to code VBA to automate the process of CTRL_ALT_ENTER required to make these formulae work? Thank you!

=INDEX('Worksheet1'!O:O,MATCH(1,('Worksheet2'!B1='Worksheet1'!B:B)*('Worksheet2'!B2='Worksheet1'!C:C)*('Worksheet2'!B3='Worksheet1'!N:N)*('Worksheet2'!A13='Worksheet1'!G:G),0))

 

[JustynaMK]

Hi Bert,

In your VBA simply replace every occurrence of Worksheet2 with Active Sheet's Name (& .Name & syntax). See below (the result of this function will be displayed in the first sheet, cell A1):

Sub IndexMatch()


    With ActiveSheet
        [COLOR=#0000ff]Sheet1.Range("A1").FormulaArray[/COLOR] = _
            "=INDEX('Worksheet1'!O:O,MATCH(1,('"[COLOR=#ff0000]& .Name &[/COLOR]"'!B1='Worksheet1'!B:B)*('"[COLOR=#ff0000]& .Name &[/COLOR]"'!B2='Worksheet1'!C:C)*('"[COLOR=#ff0000]& .Name &[/COLOR]"'!B3=' Worksheet1'!N:N)*('"[COLOR=#ff0000]& .Name &[/COLOR]"'!A13='Worksheet1'!G:G),0))"
    End With


End Sub

I also assumed that you meant Ctrl+Shift+Enter (not Ctrl+Alt+Enter), so I used .FormulaArray property.

Let me know if that works.

Best regards,
Justyna

 

[Bert_Withpeterson]

Thanks so much for your help, much appreciated.

 

[JustynaMK]

No worries, glad it helps!

Take care,
Justyna