Excel/excel 2007 help
I have a list of part numbers in Column A and I have hyper linked attached to those numbers in Column B which is a PDF specific to each part number.
Now I am taking input in Cell C1 from a scanner which is giving me item number. Now as soon as input is received in the cell C1 macro gets triggered and displays a particular pdf. Everything is working great up til now.I have pasted the full code below.
But some parts we are procuring from different suppliers. In that case part number is same but what I want to display is different. Now I want to add in this code only a command that will look for part numbers as it is looking now. In case of single part number it goes as it is going now but in case of multiple part numbers it looks for Vendor code which will be written in column D for only those part numbers and asks for vendor code and then displays the relevant document. I hope I made myself clear.Please tell me if something is not clear
Please help. Thanks in advance.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C1")) Is Nothing Then macro
Dim myR As Range
Set myR = Worksheets("Sheet1").Range("A:A").Find( _
If Not myR Is Nothing Then
ThisWorkbook.FollowHyperlink myR.Offset(, 1).Value
MsgBox "Item Number Not Found. Please check The Number You have Entered"
ANSWER: Hi Saurabh,
I understand your requirement, I have just one question-
Is it possible to fill up the vendor in all cases instead of just the repeated items? That way, the system is more fool proof.
If you can do that, then you simple need to search that row in that cell and the next column.
Alternatively, you can search for non-blank cells in the vendor column and search for the code.
Let me know so I can change the macro accordingly.
---------- FOLLOW-UP ----------
No. It is not possible to put vendor codes for each part number as there are 12000 numbers.
Here's another possible solution:
I understand that part numbers can possibly repeat across different vendors. If they repeat, you need to look at the vendor name else only the part number.
Assuming your part numbers are stored in column B and vendor is stored in column C, then, create a concatenate formula in column D where you concatenate the values of columns B & column C. Now compare this with the data captured with the scanner. (I'm assuming that the barcode scanner is capturing the vendor as well for such cases)
Let me have your thoughts on this.