Filter Month then name

[Guest]

Hi there I have this following code at the moment,

Private Sub Combobox1_Click()
Dim sh As Worksheet, rng As Range
Dim cnt As Long
If ComboBox1.ListIndex = -1 Then
MsgBox "Select a name first, then select a month"
Exit Sub
End If
Set sh = ActiveSheet
If sh.AutoFilterMode = True Then
If sh.FilterMode Then _
sh.ShowAllData
sh.AutoFilterMode = False
End If
Set rng = sh.Range(sh.Cells(1, 2), _
sh.Cells(1, 2).End(xlDown))
cnt = Evaluate("sumproduct(--(" & rng.Address & "=""" & _
ComboBox1.Value & """),--(" & rng.Offset(0, 1).Address & _
"=""" & cmbName & """))")

rng.Resize(, 3).AutoFilter Field:=1, Criteria1:=ComboBox1.Value

If cnt > 0 Then
rng.Resize(, 3).AutoFilter Field:=2, Criteria1:=cmbName.Value
Else
rng.Resize(, 3).AutoFilter Field:=3, Criteria1:=cmbName.Value
End If
End Sub

It is running off a user form that lets them select their name from a
dropdown list and then the month they wish to look at. The month is situated
in Column B and the Names are situated in Columns C & D. I want it to first
filter the month and then filter the names colum C & D (as their name may
appear in both columns) and then show the results on the screen. If they
don't have any information for that month then I want a pop up to say "No
risks for this month........" and just show the risks relating to their name.

But I am having all sorts of trouble getting it to work.

Regards

 

[Guest]

As previously answered:

cnt = Evaluate("sumproduct(--(" & rng.Address & "=""" & _
ComboBox2.Value & """),--(" & rng.Offset(0, 1).Address & _
"=""" & ComboBox1.Value & """))")

determines which column to look for the range. If cnt is zero, it doesn't
find that name for that month in the second column and therefore looks for
it in the third column. Put in a message box to display the Cnt. then see
if you agree with the count.

Your original description said to filter on one set of names and if not
found there, then filter on the second set of names instead. If you want to
display a row if it is in either or show relevant risks, then the easiest
would be to have the code add a formula in the next available column that
would indicate whether to display that row or not and simply autofilter on
that formula.

=IF(AND(OR(C2="John",D2="John"),B2="June"),"Show",IF((TRUE),"Relevant","no
show"))

True represents some formula that determines if the row is risk relevant.

so it would be something like:

rng.offset(0,10).Formula = "=if(And(Or(C2=""" & Combobox2.value & _
""",D2=""" & Combobox2.value & """)" & _
",B2=""" & Combobox1.Value & """),""Show"",if((True),""Relevant"",""no
show""))"

 

[Guest]

Hi Tom,

I've tried this but it doesn't appear to be working so I assume its me
putting it in the wrong place.

 

[Guest]

It would be hard to tell.

 

[Guest]

Its actually this bit that seems to be causing the problems

rng.Resize(, 3).AutoFilter Field:=1, Criteria1:=ComboBox1.Value

Saying its an error 1004 Application-defined or object-defined error

Which is odd as I have got it to work on other workbooks.

 

[Guest]

If you want to send me a sample workbook, I will work it out for you.

(e-mail address removed)

 

[Guest]

Where should I send it to?