OzGrid

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

Requirement:

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.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/149051-vba-for-index-and-match-based-on-sheet-criteria

Solution:

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

Gallery