You are here:

Excel/Search Columns in Excel

Advertisement


Question
Hi Tom,

I'm trying to create some search boxes in an excel database.  I would like these search boxes to have the user type in a cell and the code would then search a specific column for all partial matches to that input. I have seven search boxes corresponding to seven different columns. This is what I have so far and I have run into a dead end.

Public ClickNext
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, r1 As Range, r2 As Range, res As Variant
Dim s As String, i As Long
Dim oRange As Range
Dim SearchString As String


v1 = Array("A11", "B11", "C11", "D11", "E11", "F11", "J11")

If Target.Count > 1 Then

   Set r = Range("B2:B8")
   If Not Intersect(Target, r) Is Nothing Then
        i = Target.Row - 2
        s = v1(i)
        Set r2 = Range(s)
        SearchString = Target.Value
        Set oRange = Range(r2, Cells(Me.Rows.Count, r3.Column).End(xlUp))
        Set r1 = oRange.Find(SearchString, After:=s, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
         
         If Not r1 Is Nothing Then

         r1.Select
         Search.Show

         Do While Search.ClickNext
         Set r1 = oRange.FindNext(After:=r1)
         r1.Select
         Search.Show
         If Not r1 Is Nothing Then
         If r1.Address = r2.Address Then Exit Do
         Else
         ClickNext = False
         End If
         Loop
         
Else
 MsgBox "not found"

End If
End If
End If

End Sub



With userform "Search":

Public ClickNext

Private Sub End1_Click()
ClickNext = False
Unload Search
End Sub

Private Sub Next1_Click()

ClickNext = True
Hide

End Sub


Now I am able to have the code run correctly if I directly input the column and search string information into the macro but I can't seem to figure out how to use what the user enters into the search cells.  Any help or direction on this would be great.  

Thanks,
Josh

Answer
Josh,

I would see something like this


first, insert a new module - a standard/general module.  Go into the VBE and with your workbook as the activeproject, do Insert=>Module

In that new module put in

Public ClickNext

This way your ClickNext variable will be visible in both the sheet module and the userform module in the same way as you are referencing it.

In the worksheet module, make sure you removed the   "Public ClickNext" declaration at the top.  Then put in this modified code to replace your code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, r1 As Range, r2 As Range, res As Variant
Dim s As String, i As Long
Dim oRange As Range
Dim SearchString As String


v1 = Array("A11", "B11", "C11", "D11", "E11", "F11", "J11")

If Target.Count = 1 Then  ' error in this line.  Change > 1 to = 1

  Set r = Range("B2:B8")
  If Not Intersect(Target, r) Is Nothing Then
       i = Target.Row - 2
       s = v1(i)
       Set r2 = Range(s)
       SearchString = Target.Value
       ' error in the next line.  Change r3.Column to r2.Column
       Set oRange = Range(r2, Cells(Me.Rows.Count, r2.Column).End(xlUp))
       ' change After:=s to After:=r2
       Set r1 = oRange.Find(SearchString, _
         After:=r2, _
         LookIn:=xlValues, _
         LookAt:=xlPart, _
         SearchOrder:=xlByRows, _
         SearchDirection:=xlNext, _
         MatchCase:=False, _
         SearchFormat:=False)
        
        If Not r1 Is Nothing Then
        sAddr = r1.Address
        r1.Select
        Search.Show

        Do While Search.ClickNext
        Set r1 = oRange.FindNext(After:=r1)
        If r1.Address = sAddr Then Exit Do
        r1.Select
        Search.Show
        ' r1 will never be nothing or you wouldn't be here
'        If Not r1 Is Nothing Then
'        If r1.Address = r2.Address Then Exit Do
'        Else
'        ClickNext = False
'        End If
        Loop
        
        Else
         MsgBox "not found"

        End If
  End If
End If

End Sub

With userform "Search":

Public ClickNext


-------------
These are in the Userform Search module - I have not altered these.

Private Sub End1_Click()
ClickNext = False
Unload Search
End Sub

Private Sub Next1_Click()

ClickNext = True
Hide

End Sub


I tested this and it worked as I expected.  

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.