So my excel have tens of thousands of rows and some of them contain the same information. For example, there are part numbers that we get from multiple suppliers so I would like to use a code to find the multiple suppliers(if a part number has one).
Here is my code ( actually I got this online and customized it to my need). So far, it only returns one value(typically the first value).
By the way, I'm a beginner in vba so I'm still learning all the ternms
Sub FETCH()
On Error GoTo MyErrorHandler:
Dim PN_id As Long
PN_id = InputBox("Enter the Part Number:")
Det = "Part Number : " & Application.WorksheetFunction.VLookup(PN_id, Sheet4.Range("A2:T40018"), 1, False)
Det = Det & vbNewLine & "Part Description: " & Application.WorksheetFunction.VLookup(PN_id, Sheet4.Range("A2:T40018"), 3, False)
Det = Det & vbNewLine & "Supplier: " & Application.WorksheetFunction.VLookup(PN_id, Sheet4.Range("A2:T40018"), 11, False)
Det = Det & vbNewLine & "Car Model: " & Application.WorksheetFunction.VLookup(PN_id, Sheet4.Range("A2:T40018"), 19, False)
MsgBox "Part Details : " & vbNewLine & Det
Exit Sub
MyErrorHandler:
If Err.Number = 1004 Then
MsgBox "Part Number Not Present in the table."
ElseIf Err.Number = 13 Then
MsgBox "You have entered an invalid value."
End If
End Sub
Thanks!
Here is my code ( actually I got this online and customized it to my need). So far, it only returns one value(typically the first value).
By the way, I'm a beginner in vba so I'm still learning all the ternms
Sub FETCH()
On Error GoTo MyErrorHandler:
Dim PN_id As Long
PN_id = InputBox("Enter the Part Number:")
Det = "Part Number : " & Application.WorksheetFunction.VLookup(PN_id, Sheet4.Range("A2:T40018"), 1, False)
Det = Det & vbNewLine & "Part Description: " & Application.WorksheetFunction.VLookup(PN_id, Sheet4.Range("A2:T40018"), 3, False)
Det = Det & vbNewLine & "Supplier: " & Application.WorksheetFunction.VLookup(PN_id, Sheet4.Range("A2:T40018"), 11, False)
Det = Det & vbNewLine & "Car Model: " & Application.WorksheetFunction.VLookup(PN_id, Sheet4.Range("A2:T40018"), 19, False)
MsgBox "Part Details : " & vbNewLine & Det
Exit Sub
MyErrorHandler:
If Err.Number = 1004 Then
MsgBox "Part Number Not Present in the table."
ElseIf Err.Number = 13 Then
MsgBox "You have entered an invalid value."
End If
End Sub
Thanks!