How to create VBA for index and match based on sheet criteria

< Back to Search results

 Category: [Excel]  Demo Available 

How to create VBA for index and match based on sheet criteria




The user has a workbook with different suppliers .

The user needs to check column C of data check sheet based on criteria G2 which represents the supplier then go required sheet supplier, lookup column A , if match then extract column C and D data ,and insert it on column A and B of data check sheet of corresponding data.

If criteria in G2 data check sheet is supplier B , then check and extract matching data.

The user is looking for A VBA solution as data reaches 5000 rows on check data sheet.






Assign this to the button you are going to put on the CHECK DATA sheet.

Sub GetSupplierData()
    Dim x, y, z, i As Long, ii As Long, sSht As String
    sSht = [G2]
    x = Sheets(sSht).Cells(1).CurrentRegion
    ReDim z(1 To UBound(x, 1))
    For i = 1 To UBound(z)
        z(i) = CStr(x(i, 1))
    With Sheets("CHECK DATA")
        y = .Cells(1).CurrentRegion
        For i = 2 To UBound(y, 1)
            If Not IsError(Application.Match(CStr(y(i, 3)), z, 0)) Then
                ii = Application.Match(CStr(y(i, 3)), z, 0)
                y(i, 1) = x(ii, 3): y(i, 2) = x(ii, 4)
            End If
        .Cells(1).CurrentRegion = y
    End With
End Sub


Obtained from the OzGrid Help Forum.

Solution provided by KjBox.


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)