Using Index Match in Loop on VBA

rakotonirinas · Oct 5th 2018

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.

Tom Rowe · Oct 11th 2018

Hi,

Try...

Code

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.
Next


End Sub

Display More

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

Regards,

Tom Rowe

Using Index Match in Loop on VBA

Participate now!

Share