Auto Searching

[Harquor]

Hello. I am trying to build a quote sheet for my job. The manufacturers send me pricing sheets with their model numbers. I would have my quote sheet on the first tab and then load the price sheets on subsequest tabs. We'll call them Bobrick, Bradley and ASI.

What I would like to do is be able to type the specified item number in a cell (A1) on my quote sheet, for example B202, and have it search the other tabs for the model number, then reference the price which is in the cell to the right of the model number and populate the price in cell A2 on my quote sheet.

I don't know if something like this is even possible, but I thought I'd ask.

Thank you for any help you can offer!

 

[L. Howard]

Try this, copy to a standard module.

I am using "quote sheet" as the job sheet name. Change in the code to match your job sheet name EXACTLY.
Enter your search item in A1 on "quote sheet" and run the code.

Howard

Sub PRICE_FIND()
Dim sh As Worksheet
Dim myRng As Range
Dim psNum$

psNum = Range("A1")

For Each sh In Worksheets(Array("Bobrick", "Bradley", "ASI"))

  Set myRng = sh.UsedRange.Find(psNum, LookIn:=xlValues)
                                               
  If Not myRng Is Nothing Then
   
    Sheets("[COLOR=#FF0000]quote sheet[/COLOR]").[COLOR=#FF0000][/COLOR]Range("B1") = myRng.Offset(, 1)
    MsgBox "Sheet - " & """" & sh.Name & """" & vbCr & _
           "    Cell - " & myRng.Offset(, 1).Address
     
  End If
   
Next sh
End Sub

 

[Harquor]

Thank you Howard! I'm going to be working on this over Christmas break. I'll let you know how it turns out. Havea great day!