Search code works--sort of.

[archangel1177]

In a previous thread I was trying to get some code to work as a search function and after messing with it for a while I finally got it to work-sort of. What I have is a search function and when I run the code it does find the part in the sheet and it displays it in a message box. But it only displays the part number. I need it to also display the location in the message box as well that would shoe the cabinet number and the bin number (column e and f) as well and thats where i am stuck at the moment. I will insert the code here and I will post the sheet as well.

Sub Find_First()
    Dim FindString As String
    Dim Rng As Range
    FindString = InputBox("Enter a Search value")
    If Trim(FindString) <> "" Then
        With Sheets("ERGO II Spares").Range("B7:C486")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
                            MsgBox "Found here  " & Rng
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
            Else
                MsgBox "Nothing found"
            End If
        End With
    End If
End Sub

[archangel1177]

Here is a condensed version of the database I am working with. Thanks ahead

[TnTinMN]

MsgBox "Found here " & Rng

Rng is an object. If you wnat the address use Rng.Address.
But move to be inside your if block just in case that Rng is Nothing.

[archangel1177]

I will try this in just a bit. Thank you for your help. There are so many things I need to learn about VBA

[archangel1177]

Okay so that was a useful function but not quite what I was looking for. What I am looking to include in my message box is the location that is in the sheet in columns e and f for each part number. So when I do a search for a part number it will return the part found and where the part is located as far as cabinet and bin number.

[TnTinMN]

Is this what you mean?

MsgBox "Found here - Cabinet: " & Range("E" & Rng.Row).Value _
       & " Drawer: " & Range("F" & Rng.Row).Value

[archangel1177]

Yes this should do exactly what I need. I was on the right track but since my knowledge is so limited in VBA I was not nearly that far. I am still trying to learn the commands and their meanings and the for dummies books, while very helpful, arent progressing like I would like them to. Is there a book that is very highly recommended out there for me to get? By the way Thank you so much for your assistance!

[archangel1177]

I just tried this and it works exactly as I would like it! Thank you so much for helping me!

[TnTinMN]

Great. Please close out this thread then by marking it answered.