Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2013
    Posts
    2

    Unanswered: Assistance with excel VBA please?

    I have been working on an inventory system and since I appear to have plenty of time I decided to base it off of a system that was used once before. Right now I have figured out how to use VB to search for part numbers in the list sheet and return a result in the form of the part number being found in the list. It displays in a message box and what I am looking to do is alter what I have to add not only the part number but the location of the part into the message box as well. I have tried a few different things but I am kind of lost on this since I am pretty new to VBA. Any assistance would be appreciated. Thank you. There is a zip file of the sample that can be used for what I am doing. Again thank you for your assistance!

    Here is the code:
    Code:
    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
    Ergo prototype(2).zip

  2. #2
    Join Date
    Mar 2013
    Posts
    2
    Okay so now I have all the results that I want to see in my message box, Now I would like to be able to have it return multiple results if a partial part number is entered. I tried to work with a loop function but I am still not all that great at writing VBA. I know how to add extra lines to the message box so I can do that part. Here is what I have so far:

    Code:
    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("B2:D999")
                Set Rng = .Find(What:=FindString, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlPart, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                                    MsgBox "Found here - Item: " & Range("D" & Rng.Row).Value _
        & " Cabinet: " & Range("E" & Rng.Row).Value _
         & " Drawer " & Range("F" & Rng.Row).Value _
         & " QTY: " & Range("H" & Rng.Row).Value
         
                    
                If Not Rng Is Nothing Then
                
                    Application.Goto Rng, True
                Else
                    MsgBox "Nothing found"
                End If
            End With
        End If
    End Sub


    Thank you for any assistance!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •