No announcement yet.

Using Index Match in Loop on VBA

  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.
    Attached Files

  • #2



    Option Explicit
    ' Worksheet Handing.
    Public lngLastRow As Long
    Public lngMatchRow As Long
    Public lngRow As Long
    Public Function LastRow(objWorkSheetFindLastRow As Worksheet, intColFindLastRow As Integer) As Long
    ' Ensure that the column being passed is populated for all rows for the correct last row to be returned
    With objWorkSheetFindLastRow
         LastRow = .Cells(.Rows.Count, intColFindLastRow).End(xlUp).Row
    End With
    End Function
    Public Sub EFTest()
    lngLastRow = LastRow(Sheet2, 1)
    For lngRow = 2 To lngLastRow
        ' Match iD in Sheet2 with iD in Sheet1
        lngMatchRow = Application.Match(Sheet2.Cells(lngRow, 1), Sheet1.Columns(1), 0)
        ' Populate the data matched for column iD in Sheet1 in Sheet2
        Sheet2.Cells(lngRow, 2) = Sheet1.Cells(lngMatchRow, 2) ' No.
        Sheet2.Cells(lngRow, 3) = Sheet1.Cells(lngMatchRow, 3) ' Department No.
        Sheet2.Cells(lngRow, 4) = Sheet1.Cells(lngMatchRow, 4) ' Member No.
    End Sub
    I have attached a copy of your spreadsheet with working code.


    Tom Rowe
    Attached Files