Hi all,

Im a novice when it comes to macro's VBA etc so I would appreciate some guidance please.

I run a small service department. We have a number of bins containing various parts. Im trying to design an excel sheet that will allow engineers to search for a particular part by description or number and to be shown the relative bin number as a result.

I have searched the net for relevant code and have got pretty close to what I need. However, the search code im using returns the cell reference containing the relevant part number. What I need it to do is return the "bin reference". I have tried naming cell ranges (on the attached example only the first 4 are named) but im unable to modify the button code to return the bin reference as a result.

If anyone could please have a look at my code and let me know what I need to do I would appreciate it.

Thanks 

Sub Button2_Click()
Dim ws As Worksheet
Dim Found As Range
Dim myText As String
Dim FirstAddress As String
Dim AddressStr As String
myText = InputBox("Enter part number or description")
If myText = "" Then Exit Sub
For Each ws In ThisWorkbook.Worksheets
With ws
Set Found = .UsedRange.Find(what:=myText, LookIn:=xlValues, MatchCase:=False)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf
Set Found = .UsedRange.FindNext(Found)
Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If
End With
Next ws
If Len(AddressStr) Then
MsgBox AddressStr, vbOKOnly, myText
Else:
MsgBox "Unable to find " & myText
End If
End Sub
Test.xlsm