PDA

View Full Version : Solved: filtering on named ranges.



khalid79m
05-19-2009, 05:35 AM
My code
Private Sub DataType2()
'BY DEPT ONLY
'SITE DATA (SHEET = SITE)
If Sheets("Site").AutoFilterMode = True Then Sheets("Site").AutoFilterMode = False

If Range("Department_Value") <> "" Then
Range("Site_Department").AutoFilter Field:=1, Criteria1:=Sheets("Control").Range("C8").Value
Range("Site_Month").AutoFilter Field:=1, Criteria1:=Sheets("Control").Range("C10").Value
Range("Site_Year").AutoFilter Field:=1, Criteria1:=Sheets("Control").Range("C12").Value
End If
End Sub

What am I doing wrong ? I am tring to filter for the 3 criteria's above but it doesnt work.

example with colum ("Site_Department") look for range c8 from the control sheet & with column("Site_Month") look for the range c10 from the control sheet & with the column ("Site_Year") look for the range c12 from the control sheet and then display with results that match all 3 criteria. ..


Im baffled any help would be appreciated:help

Bob Phillips
05-19-2009, 05:42 AM
If they are adjacnet, try



Private Sub DataType2()
'BY DEPT ONLY
'SITE DATA (SHEET = SITE)
If Sheets("Site").AutoFilterMode = True Then Sheets("Site").AutoFilterMode = False

If Range("Department_Value") <> "" Then
With Range("Site_Department").Resize(, 3)

.AutoFilter Field:=1, Criteria1:=Sheets("Control").Range("C8").Value
.AutoFilter Field:=2, Criteria1:=Sheets("Control").Range("C10").Value
.AutoFilter Field:=3, Criteria1:=Sheets("Control").Range("C12").Value
End With
End If
End Sub

khalid79m
05-19-2009, 05:55 AM
They are not adjecent , can anyone else help ?

Bob Phillips
05-19-2009, 07:44 AM
Then you need to resize accordingly and set the field index accordingly.

khalid79m
05-20-2009, 02:07 AM
Hi XLD,

Could you give me some help around this, I have never done resize before,

The range "site department" is Column B, the data is held from b1 downwards.


The range "site MONTH" is Column D, the data is held from b1 downwards.
The range "site YEAR" is Column E, the data is held from b1 downwards.

Im really struggling with this so all your help is much appreciated

Bob Phillips
05-20-2009, 03:11 AM
You resize by 4 columns, B-E, and you filter on fields 1, 3 and 4



Private Sub DataType2()
'BY DEPT ONLY
'SITE DATA (SHEET = SITE)
If Sheets("Site").AutoFilterMode = True Then Sheets("Site").AutoFilterMode = False

If Range("Department_Value") <> "" Then
With Range("Site_Department").Resize(, 4)

.AutoFilter Field:=1, Criteria1:=Sheets("Control").Range("C8").Value
.AutoFilter Field:=3, Criteria1:=Sheets("Control").Range("C10").Value
.AutoFilter Field:=4, Criteria1:=Sheets("Control").Range("C12").Value
End With
End If
End Sub

khalid79m
05-20-2009, 07:08 AM
your the man !!!! nice one ..

khalid79m
05-20-2009, 07:09 AM
ill try out tonight , make sure it works for me :)

khalid79m
05-21-2009, 01:55 AM
ace.. good job