Using Index Match in Loop on VBA

  • Hello experts,
    Can you please help on the following?

    I think what I am attempting to achieve is quite simple but somehow I could not manage to make it work on VBA.

    As per my attached spreadsheet, I have 2 worksheets. The 'OCT18' is the master one and SBM_18 extract data from the master 'OCT18' one.
    Without VBA, I will simply use a Vlookup or Index Match such as the below

    =INDEX('OCT18'!B$2:B$50,MATCH(SBM_18!$A2,'OCT18'!$A$2:$A$50,0),1) > for the No. in 'SBM_18'
    =INDEX('OCT18'!C$2:C$50,MATCH(SBM_18!$A2,'OCT18'!$A$2:$A$50,0),1) > for the Department No. in 'SBM_18'
    =INDEX('OCT18'!D$2:D$50,MATCH(SBM_18!$A2,'OCT18'!$A$2:$A$50,0),1) > and for the Member No. in 'SBM_18'

    Now I could copy and paste the value from 'OCT18' (B:D) to 'SBM'(B:D) but the values in the master worksheet will get updated every week so needs some form of formulas or function to work it out.

    Also, as I have more formulas applied elsewhere and a bigger data than what is presented here, I think using a VBA Code will help for this - I hope basic - lookup function. Yet, with the following code, I'm kind of stuck and unsure how to resolve the issue.

    I appreciate your help.

  • Hi,


    I have attached a copy of your spreadsheet with working code.


    Tom Rowe

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!