Filter
 

Hi,

I have set up a user form with two drop downs one for the persons name and
the other for the current month. What I want it to do is when they select
their name it will filter through Column C and just show the information
relating to them and if its not in this Column to then filter through Column
D (this is because there is a primary and secondary delegate in case of
abscence). With the month this looks at Column B as they have to look at
certain bits at certain times and i would also like this filtered. Here is an
example of how it looks.

Month Of
Review Primary person Secondary person
July Charles Stewart-Jenssen Richard Anderson

How can I achieve this?

Regards

Filter
 

Dim rng1 as Range, rng2 as Range
dim cell as Range, rng as Range
Dim bfound as Boolean

set rng = Range(cells(2,1),cells(rows.count,1).End(xlup))
for each cell in rng
if cell.Value = Combobox2.Value then
if cell.offset(0,1).Value = Combobox2.Value then
bfound = True
set rng1 = cell
exit for
end if
if cell.offset(0,2).Value = Combobox2.Value then
set rng2 = cell
end if
end if
Next
if not bFound then
if not rng2 is nothing then
set rng1 = rng2
else
MsgBox "Not found"
exit sub
end if
End if
Msgbox "Found at row: " & rng1.row

--
Regards,
Tom Ogilvy

"Pasty" wrote:

Hi,

I have set up a user form with two drop downs one for the persons name and
the other for the current month. What I want it to do is when they select
their name it will filter through Column C and just show the information
relating to them and if its not in this Column to then filter through Column
D (this is because there is a primary and secondary delegate in case of
abscence). With the month this looks at Column B as they have to look at
certain bits at certain times and i would also like this filtered. Here is an
example of how it looks.

Month Of
Review Primary person Secondary person
July Charles Stewart-Jenssen Richard Anderson

How can I achieve this?

Regards

Filter
 

Where would this code go? I a module, userform or on the actual worksheet?

"Tom Ogilvy" wrote:

Dim rng1 as Range, rng2 as Range
dim cell as Range, rng as Range
Dim bfound as Boolean

set rng = Range(cells(2,1),cells(rows.count,1).End(xlup))
for each cell in rng
if cell.Value = Combobox2.Value then
if cell.offset(0,1).Value = Combobox2.Value then
bfound = True
set rng1 = cell
exit for
end if
if cell.offset(0,2).Value = Combobox2.Value then
set rng2 = cell
end if
end if
Next
if not bFound then
if not rng2 is nothing then
set rng1 = rng2
else
MsgBox "Not found"
exit sub
end if
End if
Msgbox "Found at row: " & rng1.row

--
Regards,
Tom Ogilvy

"Pasty" wrote:

Hi,

I have set up a user form with two drop downs one for the persons name and
the other for the current month. What I want it to do is when they select
their name it will filter through Column C and just show the information
relating to them and if its not in this Column to then filter through Column
D (this is because there is a primary and secondary delegate in case of
abscence). With the month this looks at Column B as they have to look at
certain bits at certain times and i would also like this filtered. Here is an
example of how it looks.

Month Of
Review Primary person Secondary person
July Charles Stewart-Jenssen Richard Anderson

How can I achieve this?

Regards

Filter
 

After rereading you post, I had second thoughts.

This should be the click event for Combobox2 (Month name)

Private Sub Combobox2_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 & "=""" & _
ComboBox2.Value & """),--(" & rng.Offset(0, 1).Address & _
"=""" & ComboBox1.Value & """))")
rng.Resize(, 3).AutoFilter Field:=1, _
Criteria1:=ComboBox2.Value

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

--
Regards,
Tom Ogilvy

"Pasty" wrote:

Where would this code go? I a module, userform or on the actual worksheet?

"Tom Ogilvy" wrote:

Dim rng1 as Range, rng2 as Range
dim cell as Range, rng as Range
Dim bfound as Boolean

set rng = Range(cells(2,1),cells(rows.count,1).End(xlup))
for each cell in rng
if cell.Value = Combobox2.Value then
if cell.offset(0,1).Value = Combobox2.Value then
bfound = True
set rng1 = cell
exit for
end if
if cell.offset(0,2).Value = Combobox2.Value then
set rng2 = cell
end if
end if
Next
if not bFound then
if not rng2 is nothing then
set rng1 = rng2
else
MsgBox "Not found"
exit sub
end if
End if
Msgbox "Found at row: " & rng1.row

--
Regards,
Tom Ogilvy

"Pasty" wrote:

Hi,

I have set up a user form with two drop downs one for the persons name and
the other for the current month. What I want it to do is when they select
their name it will filter through Column C and just show the information
relating to them and if its not in this Column to then filter through Column
D (this is because there is a primary and secondary delegate in case of
abscence). With the month this looks at Column B as they have to look at
certain bits at certain times and i would also like this filtered. Here is an
example of how it looks.

Month Of
Review Primary person Secondary person
July Charles Stewart-Jenssen Richard Anderson

How can I achieve this?

Regards

Filter
 

Its a step in the right direction but it comes up with that end/debug error
and highlights this line

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

"Tom Ogilvy" wrote:

After rereading you post, I had second thoughts.

This should be the click event for Combobox2 (Month name)

Private Sub Combobox2_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 & "=""" & _
ComboBox2.Value & """),--(" & rng.Offset(0, 1).Address & _
"=""" & ComboBox1.Value & """))")
rng.Resize(, 3).AutoFilter Field:=1, _
Criteria1:=ComboBox2.Value

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

--
Regards,
Tom Ogilvy

"Pasty" wrote:

Where would this code go? I a module, userform or on the actual worksheet?

"Tom Ogilvy" wrote:

Dim rng1 as Range, rng2 as Range
dim cell as Range, rng as Range
Dim bfound as Boolean

set rng = Range(cells(2,1),cells(rows.count,1).End(xlup))
for each cell in rng
if cell.Value = Combobox2.Value then
if cell.offset(0,1).Value = Combobox2.Value then
bfound = True
set rng1 = cell
exit for
end if
if cell.offset(0,2).Value = Combobox2.Value then
set rng2 = cell
end if
end if
Next
if not bFound then
if not rng2 is nothing then
set rng1 = rng2
else
MsgBox "Not found"
exit sub
end if
End if
Msgbox "Found at row: " & rng1.row

--
Regards,
Tom Ogilvy

"Pasty" wrote:

Hi,

I have set up a user form with two drop downs one for the persons name and
the other for the current month. What I want it to do is when they select
their name it will filter through Column C and just show the information
relating to them and if its not in this Column to then filter through Column
D (this is because there is a primary and secondary delegate in case of
abscence). With the month this looks at Column B as they have to look at
certain bits at certain times and i would also like this filtered. Here is an
example of how it looks.

Month Of
Review Primary person Secondary person
July Charles Stewart-Jenssen Richard Anderson

How can I achieve this?

Regards

Filter
 

Its a step in the right direction
Thats cute! <g

Must be you/your layout. the code was taken from a module where it
performed as designed. You will have to tweak it to your layout.
--
Regards,
Tom Ogilvy

"Pasty" wrote in message
...
Its a step in the right direction but it comes up with that end/debug
error
and highlights this line

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

"Tom Ogilvy" wrote:

After rereading you post, I had second thoughts.

This should be the click event for Combobox2 (Month name)

Private Sub Combobox2_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 & "=""" & _
ComboBox2.Value & """),--(" & rng.Offset(0, 1).Address & _
"=""" & ComboBox1.Value & """))")
rng.Resize(, 3).AutoFilter Field:=1, _
Criteria1:=ComboBox2.Value

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

--
Regards,
Tom Ogilvy

"Pasty" wrote:

Where would this code go? I a module, userform or on the actual
worksheet?

"Tom Ogilvy" wrote:

Dim rng1 as Range, rng2 as Range
dim cell as Range, rng as Range
Dim bfound as Boolean

set rng = Range(cells(2,1),cells(rows.count,1).End(xlup))
for each cell in rng
if cell.Value = Combobox2.Value then
if cell.offset(0,1).Value = Combobox2.Value then
bfound = True
set rng1 = cell
exit for
end if
if cell.offset(0,2).Value = Combobox2.Value then
set rng2 = cell
end if
end if
Next
if not bFound then
if not rng2 is nothing then
set rng1 = rng2
else
MsgBox "Not found"
exit sub
end if
End if
Msgbox "Found at row: " & rng1.row

--
Regards,
Tom Ogilvy

"Pasty" wrote:

Hi,

I have set up a user form with two drop downs one for the persons
name and
the other for the current month. What I want it to do is when they
select
their name it will filter through Column C and just show the
information
relating to them and if its not in this Column to then filter
through Column
D (this is because there is a primary and secondary delegate in
case of
abscence). With the month this looks at Column B as they have to
look at
certain bits at certain times and i would also like this filtered.
Here is an
example of how it looks.

Month Of
Review Primary person Secondary person
July Charles Stewart-Jenssen Richard Anderson

How can I achieve this?

Regards

Filter
 

Ta, I got that working but for some reason it is only searching on the second
list of names so if they appear on the the first column it says they have
nothing to view, whereas I want it to search both and show them (as some
names appear in both columns) and I am having a bit of trouble working this
out.

Also if they have nothing for that month ideally I would like it to display
a pop up message and then just show all the risks relevant to them rather
than showing nothing at all.

"Tom Ogilvy" wrote:

Its a step in the right direction
Thats cute! <g

Must be you/your layout. the code was taken from a module where it
performed as designed. You will have to tweak it to your layout.
--
Regards,
Tom Ogilvy

"Pasty" wrote in message
...
Its a step in the right direction but it comes up with that end/debug
error
and highlights this line

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

"Tom Ogilvy" wrote:

After rereading you post, I had second thoughts.

This should be the click event for Combobox2 (Month name)

Private Sub Combobox2_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 & "=""" & _
ComboBox2.Value & """),--(" & rng.Offset(0, 1).Address & _
"=""" & ComboBox1.Value & """))")
rng.Resize(, 3).AutoFilter Field:=1, _
Criteria1:=ComboBox2.Value

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

--
Regards,
Tom Ogilvy

"Pasty" wrote:

Where would this code go? I a module, userform or on the actual
worksheet?

"Tom Ogilvy" wrote:

Dim rng1 as Range, rng2 as Range
dim cell as Range, rng as Range
Dim bfound as Boolean

set rng = Range(cells(2,1),cells(rows.count,1).End(xlup))
for each cell in rng
if cell.Value = Combobox2.Value then
if cell.offset(0,1).Value = Combobox2.Value then
bfound = True
set rng1 = cell
exit for
end if
if cell.offset(0,2).Value = Combobox2.Value then
set rng2 = cell
end if
end if
Next
if not bFound then
if not rng2 is nothing then
set rng1 = rng2
else
MsgBox "Not found"
exit sub
end if
End if
Msgbox "Found at row: " & rng1.row

--
Regards,
Tom Ogilvy

"Pasty" wrote:

Hi,

I have set up a user form with two drop downs one for the persons
name and
the other for the current month. What I want it to do is when they
select
their name it will filter through Column C and just show the
information
relating to them and if its not in this Column to then filter
through Column
D (this is because there is a primary and secondary delegate in
case of
abscence). With the month this looks at Column B as they have to
look at
certain bits at certain times and i would also like this filtered.
Here is an
example of how it looks.

Month Of
Review Primary person Secondary person
July Charles Stewart-Jenssen Richard Anderson

How can I achieve this?

Regards

Filter
 

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""))"



--
Regards,
Tom Ogilvy




"Pasty" wrote in message
...
Ta, I got that working but for some reason it is only searching on the
second
list of names so if they appear on the the first column it says they have
nothing to view, whereas I want it to search both and show them (as some
names appear in both columns) and I am having a bit of trouble working
this
out.

Also if they have nothing for that month ideally I would like it to
display
a pop up message and then just show all the risks relevant to them rather
than showing nothing at all.

"Tom Ogilvy" wrote:

Its a step in the right direction
Thats cute! <g

Must be you/your layout. the code was taken from a module where it
performed as designed. You will have to tweak it to your layout.
--
Regards,
Tom Ogilvy

"Pasty" wrote in message
...
Its a step in the right direction but it comes up with that end/debug
error
and highlights this line

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

"Tom Ogilvy" wrote:

After rereading you post, I had second thoughts.

This should be the click event for Combobox2 (Month name)

Private Sub Combobox2_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 & "=""" & _
ComboBox2.Value & """),--(" & rng.Offset(0, 1).Address & _
"=""" & ComboBox1.Value & """))")
rng.Resize(, 3).AutoFilter Field:=1, _
Criteria1:=ComboBox2.Value

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

--
Regards,
Tom Ogilvy

"Pasty" wrote:

Where would this code go? I a module, userform or on the actual
worksheet?

"Tom Ogilvy" wrote:

Dim rng1 as Range, rng2 as Range
dim cell as Range, rng as Range
Dim bfound as Boolean

set rng = Range(cells(2,1),cells(rows.count,1).End(xlup))
for each cell in rng
if cell.Value = Combobox2.Value then
if cell.offset(0,1).Value = Combobox2.Value then
bfound = True
set rng1 = cell
exit for
end if
if cell.offset(0,2).Value = Combobox2.Value then
set rng2 = cell
end if
end if
Next
if not bFound then
if not rng2 is nothing then
set rng1 = rng2
else
MsgBox "Not found"
exit sub
end if
End if
Msgbox "Found at row: " & rng1.row

--
Regards,
Tom Ogilvy

"Pasty" wrote:

Hi,

I have set up a user form with two drop downs one for the
persons
name and
the other for the current month. What I want it to do is when
they
select
their name it will filter through Column C and just show the
information
relating to them and if its not in this Column to then filter
through Column
D (this is because there is a primary and secondary delegate in
case of
abscence). With the month this looks at Column B as they have to
look at
certain bits at certain times and i would also like this
filtered.
Here is an
example of how it looks.

Month Of
Review Primary person Secondary person
July Charles Stewart-Jenssen Richard Anderson

How can I achieve this?

Regards