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

Recommended Answers

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.

Jump to Post

Is this what you mean?

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

All 8 Replies

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.

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

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.

Is this what you mean?

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

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!

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.