Can This Be Solved?

WorkMC

Board Regular
I'm using the code below that I found on the internet to filter data with a userform and list box. I thought everything was working OK but have discovered a slight problem that I'm unable to resolve.

If for example A,AB,ABC,B,BB.BBC appears in the drop downlist and I select A, then not only do records A appear, but AB and ABC as well. Can anyone advise how to select an exact match only?

Dim strOperator1 As String, strOperator2 As String
Dim strOperator3 As String, strOperator4 As String
Dim rCell As Range

With Sheet2
On Error Resume Next
'Clear extract range and Criteria range

'Put in criteria as chosen
If Dand.Value = True Then
If D1.ListIndex > -1 Then .Range("B4") = "=" & """" & D1.Value & """"
If D2.ListIndex > -1 Then .Range("C4") = "=" & """" & D2.Value & """"
Else 'It's or
If D1.ListIndex > -1 Then .Range("B4") = "=" & """" & D1.Value & """"
If D2.ListIndex > -1 Then .Range("B5") = "=" & """" & D2.Value & """"
End If

If Qand.Value = True Then
If Q1.ListIndex > -1 Then .Range("D4") = Q1C & Q1.Value
If Q2.ListIndex > -1 Then .Range("E4") = Q2C & Q2.Value
Else 'It's or
If Q1.ListIndex > -1 Then .Range("D4") = Q1C & Q1.Value
If Q2.ListIndex > -1 Then .Range("D5") = Q2C & Q2.Value
End If

'Check if any criteria has been added
If WorksheetFunction.CountA(Range("FisrtRowCriteria")) > 0 Then
'Fill in needed blank cells
For Each rCell In Range("SecondRowCriteria")
If IsEmpty(rCell) And rCell.Offset(-1, 0) <> "" Then
rCell = rCell.Offset(-1, 0)
End If
Next rCell

'Set the filter criteria range according to entries
If WorksheetFunction.CountA(Range("SecondRowCriteria")) > 0 Then
.Range(.Range("A4").End(xlToRight).Offset(-1, 0), _
.Range("L5").End(xlToLeft)).Name = "FilterCriteria"
Else
.Range(.Range("A4").End(xlToRight).Offset(-1, 0), _
.Range("L4").End(xlToLeft)).Name = "FilterCriteria"
End If

Action:=xlFilterCopy, CriteriaRange:=Range("FilterCriteria"), CopyToRange:=.Range("Z1")
'Name the newly created filtered table
.Range("Z1").CurrentRegion.Offset(1, 0).Name = "Filtered_Data"
ListBox2.RowSource = ""
ListBox2.RowSource = "Filtered_Data"
End If
End With

On Error GoTo 0

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
What appears in your Criteria range if you select A?

If I select 'A' then A, AB and ABC appear. If I select 'AB' then AB and ABC are shown in the results.

WorkMC said:
If I select 'A' then A, AB and ABC appear. If I select 'AB' then AB and ABC are shown in the results.

No, I mean what appears in the Criteria range on your worksheet?

Sorry, misunderstood. The data is held in sheet1 and the criteria are obtained from this. They are placed into sheet2 so that they only show the once. These then feed into combo boxes on the userform.

Would you like me to send you a copy of the original file I'm using so that you can see exactly what is happening?

WorkMC said:
Sorry, misunderstood. The data is held in sheet1 and the criteria are obtained from this. They are placed into sheet2 so that they only show the once. These then feed into combo boxes on the userform.

Would you like me to send you a copy of the original file I'm using so that you can see exactly what is happening?

Post up what your sheet looks like when you select A, using Colo's HTML Maker below if possible.

Here's everything except the user form.

1. Raw Data
RawFile-DoNotOverwrite.xls
ABCDE
1DescriptionNo RequiredAuctionBid DatePrice
2A281/Oct/20051/May/2005High
3A301/May/200519/Mar/2005High
4AB2519/Mar/20051/Jul/2005Low
5AB301/Jul/200529/Oct/2005Low
6ABC1529/Oct/20055/Oct/2005Medium
7ABC205/Oct/20053/May/2005High
8ABC213/May/200512/Jan/2005Low
9ABC4512/Jan/200516/Nov/2005Medium
10ABC4516/Nov/20055/Mar/2005High
11ABC455/Mar/200514/Aug/2005High
12ABC3214/Aug/20051/Jan/2005High
Data

3. Where criteria are shown once selected from the userform.

4. Results that are displayed in spreadsheet
RawFile-DoNotOverwrite.xls
1DescriptionNo RequiredAuctionBid DatePrice
2A281/Oct/20051/May/2005High
3A301/May/200519/Mar/2005High
4AB2519/Mar/20051/Jul/2005Low
5AB301/Jul/200529/Oct/2005Low
6ABC1529/Oct/20055/Oct/2005Medium
7ABC205/Oct/20053/May/2005High
8ABC213/May/200512/Jan/2005Low
9ABC4512/Jan/200516/Nov/2005Medium
10ABC4516/Nov/20055/Mar/2005High
11ABC455/Mar/200514/Aug/2005High
12ABC3214/Aug/20051/Jan/2005High
13ABC451/Jan/20051/Jul/2005High
14ABC451/Jul/20051/Feb/2005High
15ABC321/Feb/20051/Sep/2005High
16ABC281/Sep/20057/Sep/2005High
17ABC307/Sep/20051/Nov/2005Low
18ABC201/Nov/20051/Jun/2005High
19ABC251/Jun/200516/Sep/2005Low
20ABC3116/Sep/20054/Feb/2005High
21ABC34/Feb/20057/Apr/2005Low
22ABC207/Apr/200514/Mar/2005High
23ABC2814/Mar/20051/Mar/2005High
24ABC301/Mar/20051/Oct/2005Low
Workings

From Help:

When you use text as criteria with an advanced filter, Microsoft Excel finds all items that begin with that text. For example, if you type the text Dav as a criterion, Microsoft Excel finds "Davolio," "David," and "Davis." To match only the specified text, type the following formula, where text is the text you want to find.

="=text"

So your criteria in B4 and B5 should bethe formula:

="=A"

You will need to amend your code accordingly.

Replies
8
Views
160
Replies
0
Views
246
Replies
1
Views
165
Replies
10
Views
222
Replies
5
Views
424

1,214,772
Messages
6,121,459
Members
449,033
Latest member
Kcolwell16

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back