lucky12341
Board Regular
- Joined
- Nov 4, 2005
- Messages
- 121
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.
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.
Code:
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