Advanced find

[Djblois]

I found some code in a box I just bought "Excel Hacks" to create an advanced find dialog box. However, I am in the process of trying to make it better. Some of the things I am doing to it I know how to do but some other things I need help with.

First, These lines of code are filling comboboxes with whatever values show up in their column. Two Problems: if the value shows up more than once, it will show up more than once; It isn't sorted alphabetically or numerically. can I show only unique values and can I sort it before I fill the list box?

[VBA]With rRange
'Set RowSource of ComboBoxes to the appropriate coumns inside the table
Me.comFind1.RowSource = .Columns(1).Offset(1, 0).Address
Me.comFind2.RowSource = .Columns(2).Offset(1, 0).Address
Me.comFind3.RowSource = .Columns(3).Offset(1, 0).Address
Me.comFind4.RowSource = .Columns(4).Offset(1, 0).Address
Me.comFind5.RowSource = .Columns(5).Offset(1, 0).Address
End With[/VBA]

[Aussiebear]

What's stopping you from sorting your data then calling for the combobox to be filled?

[Djblois]

Because if the user is searching based on multiple columns, then how would I sort? Which column would I sort by? I can't sort by all of them.

The way the advanced find works is it allows a user to search for row that meet multiple criteria in multiple columns.

[Norie]

Daniel

As far as I can see you won't be able to adapt that code to do what you want.

Firstly RowSource requires a contiguous range so that rules out only showing uniques.

Secondly there's nothing there that's doing any sort of sorting.

There are techniques to do both of these.

I'm afraid I don't have time to post anything but I'm sure a forum search will come up with something.

[Djblois]

Can anyone point me to where I may find this? I am trying to do a forum search but have not found it?

[Djblois]

I was going to break it up but here is the full code:

[VBA]Option Explicit
Dim rRange As Range
Dim strFind1 As String
Dim strFind2 As String
Dim strFind3 As String
Dim strFind4 As String
Dim strFind5 As String
Private Sub btnClose_Click()

Unload Me
End

End Sub
Private Sub btnFind_Click()

Dim lCount As Long
Dim lOccur As Long
Dim rCell As Range
Dim rCell2 As Range
Dim rCell3 As Range
Dim rCell4 As Range
Dim rCell5 As Range
Dim bFound As Boolean

'Clear any old entries
On Error Resume Next
Me.lbResults.Clear
Me.lbValue.Clear
On Error GoTo 0

'If string variable are empty pass the wildcard character
If strFind2 = vbNullString Then strFind2 = "*"
If strFind3 = vbNullString Then strFind3 = "*"
If strFind4 = vbNullString Then strFind4 = "*"
If strFind5 = vbNullString Then strFind5 = "*"

'Set rang variable to first cell in table
Set rCell = rRange.Cells(1, 1)
'Pass the number of times strfind1 occurs
lOccur = WorksheetFunction.CountIf(rRange.Columns(1), strFind1)

'loop only as many times as strfind1 occurs
For lCount = 1 To lOccur
'set the range variable to the found cell. This is then also _
used to start the next Find from (After:=Cell)
Set rCell = rRange.Columns(1).Find(what:=strFind1, after:=rCell, _
LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False)
'check to see if other strings occur on the same row
If rCell(1, 2) Like strFind2 And rCell(1, 3) Like strFind3 _
And rCell(1, 4) Like strFind4 And rCell(1, 5) Like strFind5 Then
bFound = True 'used to not show message box for no value found.
'Add the address of the found cell and the cell on the _
same row but 2 columns to the right.
Me.lbResults.AddItem "Row" & rCell.Row
Me.lbValue.AddItem rCell(1, 5).Value
End If
Next lCount

If bFound = False Then 'No match
MsgBox "Sorry, no matches!", vbOKOnly
End If
End Sub
Private Sub comFind1_Change()
'Pass Chosen Value to String Variable strFind1
strFind1 = comFind1.Value
'enable comFind2 only if value is chosen
comFind2.Enabled = Not strFind1 = vbNullString
comCol2.Enabled = Not strFind1 = vbNullString

End Sub
Private Sub comFind2_Change()

'Pass Chosen Value to String Variable strFind2
strFind2 = comFind2.Value
'enable comFind2 only if value is chosen
comFind3.Enabled = Not strFind2 = vbNullString
comCol3.Enabled = Not strFind2 = vbNullString

End Sub
Private Sub comFind3_Change()

'Pass Chosen Value to String Variable strFind2
strFind3 = comFind3.Value
'enable comFind2 only if value is chosen
comFind4.Enabled = Not strFind3 = vbNullString
comCol4.Enabled = Not strFind3 = vbNullString

End Sub
Private Sub comFind4_Change()

'Pass Chosen Value to String Variable strFind2
strFind4 = comFind4.Value
'enable comFind2 only if value is chosen
comFind5.Enabled = Not strFind4 = vbNullString
comCol5.Enabled = Not strFind4 = vbNullString

End Sub
Private Sub comFind5_Change()

'Pass Chosen Value to String Variable strFind2
strFind5 = comFind5.Value

End Sub


Private Sub lbResults_Click()

'Check for Range Addresses
If Me.lbResults.ListCount = 0 Then Exit Sub
'goto double clicked address
Application.Goto Range(lbResults.Text), True

End Sub

Private Sub UserForm_Initialize()

Dim lrows As Long

'Set module level range variable to CurrentRegion of the selection
Set rRange = Selection.CurrentRegion
If rRange.Rows.Count < 2 Then 'Only 1 row
MsgBox "Please Select any cell in your table first", vbCritical
Unload Me 'close userform
Exit Sub
Else
With rRange
'Set RowSource of ComboBoxes to the appropriate coumns inside the table
Me.comFind1.RowSource = .Columns(1).Offset(1, 0).Address
Me.comFind2.RowSource = .Columns(2).Offset(1, 0).Address
Me.comFind3.RowSource = .Columns(3).Offset(1, 0).Address
Me.comFind4.RowSource = .Columns(4).Offset(1, 0).Address
Me.comFind5.RowSource = .Columns(5).Offset(1, 0).Address
End With

Me.comCol1.Text = Range(Cells(1, 1), Cells(1, FinalColumn(ActiveSheet))).Value

End If

End Sub
Private Sub UserForm_Terminate()

Set rRange = Nothing
strFind1 = vbNullString
strFind2 = vbNullString
strFind3 = vbNullString
strFind4 = vbNullString
strFind5 = vbNullString

End Sub[/VBA]

I am trying to do about 10 things, that I am trying to figure out:

1) I want to be able to sort the data that fills the combo boxes
2) I want to the combobox to only show unique values
3) I have another set of comboboxes that will allow the user the pick the column to do the search in
4) This second set of comboboxes need to be filled with the column names in row 1

I also have a few others but they will based on the answer to these problems. I have tried to do a forum search to no avail.

[Norie]

Daniel

Well I found this after looking for about a minute.

[Djblois]

norie please don't be rude. I did a search for "combobox fill range", and I did not get that. What search did you do? and thank you for the help.

[Norie]

Daniel

In what way am I being rude?

I've searched for what you want for part of your question and posted a link for you.

I can't recall exactly what search terms I used but I think they were 'combobox, fill, unique, values'.

Which I believe is what you want to do.

[Djblois]

Norie,

I am sorry if I misinterputed your comment as rude. I thought you were being rude by ending your comment with "after looking for about a minute."

Sorry again
Daniel

[Norie]

Daniel

I apologise if it appeared rude.

Did you try the code in the thread I posted the link to?

I know it doesn't solve all your issues, and will need to be adapted, but it specifically deals with populating a combobox with unique values.