Excel/excel 2007 help

Advertisement


Question
QUESTION: Sir,
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.

Yours Sincerely
Saurabh Mundeja

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("C1")) Is Nothing Then macro
End Sub
Sub macro()
     Dim myR As Range
     Set myR = Worksheets("Sheet1").Range("A:A").Find( _
         What:=Worksheets("Sheet1").Range("C1").Value, LookIn:=xlFormulas)
       If Not myR Is Nothing Then
         ThisWorkbook.FollowHyperlink myR.Offset(, 1).Value
         
       Else
         MsgBox "Item Number Not Found. Please check The Number You have Entered"
         End If
End Sub

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.

Thanks,
Gulshan.

---------- FOLLOW-UP ----------

QUESTION: Sir,
No. It is not possible to put vendor codes for each part number as there are 12000 numbers.

Answer
Okay Saurabh,

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.
Gulshan.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


GULSHAN PURSWANI

Expertise

I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.

Experience

I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Education/Credentials
Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 About.com. All rights reserved.