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.
Solution:
Assign this to the button you are going to put on the CHECK DATA sheet.
Code:
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
