Minimize this VBA search code

[lucky12341]

I have a userform that searches for a variety of things. The form has has 8 textboxes and a "findall" button next to each textox. To search results are then sent to a listbox where I can edit/delete/print the data. I have 8 different buttons because I cannot figure out how to set the range or offsets to fill the listbox properly based on where the information is typed. I really want to limit the form to a single "findall" button. The code below is what I use to search for the first textbox, only the columns that are searched are what change.
Textbox1=Searches column A
Textbox2=Searches column B
Textbox3=Searches column C
etc...up to textbox 8
The offsets for listbox1 are based off what column is searched also. This is where I cannot figure the code out to make it one simple string instead of 8 different buttons.
Can I have one button to search based on what textbox has information and have the search properly offset the information placed in the listbox? I have learned alot of VBA in the last month and I cannot figure this out. I even rearranged the code using checkboxes.

Private Sub cmdLocationFindAll_Click()
    Dim MyArray(500, 8)
    Dim FirstAddress As String
    Dim strFind As String
    Dim rSearch As Range
    Dim fndA, fndB, fndC, fndD, fndE, fndF, fndG, fndH, fndI As String
    Dim i As Integer
    i = 1
    Set rSearch = Sheet10.Range("a2", Range("a10425").End(xlUp))
    strFind = Me.txtLocation.Value
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then
            c.Select
            With Me.ListBox1
                MyArray(0, 0) = Range("a2").Value
                MyArray(0, 1) = Range("b2").Value
                MyArray(0, 2) = Range("c2").Value
                MyArray(0, 3) = Range("d2").Value
                MyArray(0, 4) = Range("e2").Value
                MyArray(0, 5) = C Qty"
                MyArray(0, 6) = Range("g2").Value
                MyArray(0, 7) = Range("h2").Value
                MyArray(0, 8) = "O Qty"
            End With
            FirstAddress = c.Address
            Do
                fndA = c.Value
                fndB = c.Offset(0, 1).Value
                fndC = c.Offset(0, 2).Value
                fndD = c.Offset(0, 3).Value
                fndE = c.Offset(0, 4).Value
                fndF = c.Offset(0, 5).Value
                fndG = c.Offset(0, 6).Value
                fndH = c.Offset(0, 7).Value
                fndI = c.Offset(0, 8).Value
                MyArray(i, 0) = fndA
                MyArray(i, 1) = fndB
                MyArray(i, 2) = fndC
                MyArray(i, 3) = fndD
                MyArray(i, 4) = fndE
                MyArray(i, 5) = fndF
                MyArray(i, 6) = fndG
                MyArray(i, 7) = fndH
                MyArray(i, 8) = fndI
                i = i + 1
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
        End If
    End With
    Me.ListBox1.List() = MyArray
End Sub

 

[Cbrine]

If would suggest something like this.

If textbox1 <>"" then
Search column A code
if Textbox2 <> "" then
Seach column B code

etc....

I would suggest you build an on_change event for each of the textbox, so that when the user enters data in any one of them, it clears all the rest. Otherwise you need to deal with multiple textboxes of data in your code.

HTH
Cal

 

[lucky12341]

I have never heard of an on change event code. I just searched through the database and am still looking over the examples and stuff and I cannot find it there either, can you elaborate or give an example, I can figure most code out from there.

Update-I figured out what an on change event is and understand what you mean now. How would I reflect an on change event for a textbox value not being blank though?

 

[lucky12341]

Is it possible to give me a quick example for the on_change event code?

 

[Cbrine]

Lucky,
This is the text box change event.

Private Sub TextBox1_Change()

End Sub

Just add your textbox to the form, and double click on it. Put your code in the sub that is built by the doubleclik.

HTH
Cal