CODEWITHSUNDEEP
excelvba
JEC
JEC

Reputation: 1

Search two specific, nonadjacent columns for value in textbox

Please help. I can not figure out how to get this to only search columns B and J exclusively. It is searching the range B:B through J:J. Everything else works fine.

Sub Find_Item(SNfound, SNRng, IDFound)

'The user is prompted to input either a serial number or unique ID number into a textbox on a userform. 'This is suppose to search only columns B (serial number) and J (ID number) in Table2 for the number the user entered 'Everything works except it is not limiting the search to only columns B and J. It is searching all columns from B through J.

Dim FindSNID As String 'note -- SNfound and IDFound are Dim As Boolean, SNRng is Dim As Range

Call ResetFilters 'this sub removes all filtering from the active sheet

FindSNID = SNID_textbox.Value
    If Trim(FindSNID) <> "" Then
    With Sheets("Inventory").Range("B:B", "J:J")

        Set SNRng = .Find(What:=FindSNID, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not SNRng Is Nothing Then

            SNRng.Activate

'If a match was found in column B (serial number) then display a MsgBox that the item was found and it's self location is xxxxxx from column W (offset 0,21)_ 'and it's current status is either available or checked out (offset 0,23)

            If SNRng.Column = 2 Then
            MsgBox "A matching serial number was found in location " & SNRng.Offset(0, 21).Value & vbCrLf & _
            "It's current status is " & SNRng.Offset(0, 23).Value
            Areabox2.Value = SNRng.Offset(0, 28).Value
            Sectionbox2.Value = SNRng.Offset(0, 29).Value
            Shelfbox2.Value = SNRng.Offset(0, 30).Value
            SNfound = True
            IDFound = False
            End If

'If a match is found in column J (ID Number)then the item's shelf location and status is displayed.

            If SNRng.Column = 10 Then
            MsgBox "A matching ID number was found in location " & SNRng.Offset(0, 13).Value & vbCrLf & _
            "It's current status is " & SNRng.Offset(0, 15).Value
            Areabox2.Value = SNRng.Offset(0, 28).Value
            Sectionbox2.Value = SNRng.Offset(0, 29).Value
            Shelfbox2.Value = SNRng.Offset(0, 30).Value
            SNfound = False
            IDFound = True
            End If
        End If
    End With
    End If

End Sub

Upvotes: 0

Views: 94

Answers (2)

JEC
JEC

Reputation: 1

I finally figured out how to get the Union method to work. I changed this:

Set SNRng = .Find(What:=FindSNID, _

To this:

Set SNRng = Union(Range("B:B"), Range("J:J")).Find(What:=FindSNID, _

Now the search only searches columns B and J.

Upvotes: 0

aebailey
aebailey

Reputation: 195

Try creating the range by setting a range variable = Union(Range("B:B"), Range("J:J"))

Upvotes: 0

Related Questions