 |
06-04-2003, 11:31 PM
|
|
Newcomer
|
|
Join Date: Jun 2003
Posts: 12
|
|
Filtering A List
|
how can i filter a list using combobox .... then then copy the filtered list to new range
attached is the sample...
|
|
06-05-2003, 01:39 AM
|
 |
Down...
Retired Moderator
* Expert *
|
|
Join Date: Dec 2002
Location: Belgium.
Posts: 6,731
|
|
Hmmm,i assume that you want an unique list of all the districts in
ComboBox1 so...
In this example i did put an CommandButton on sheet "MMSC".
(the button is not needed but only as an example).
Code:
Private Sub CommandButton1_Click()
Dim R As Range, UniqVal As Variant
Dim UniqList As New Collection
ComboBox1.Clear 'clear the combo otherwise we gonna add items each time we run the code
On Error Resume Next
For Each R In Sheets("Data").Range("C2", Sheets("Data").Range("C2").End(xlDown))
UniqList.Add R.Value, CStr(R.Value) 'create a collection of unique values
Next R
For Each UniqVal In UniqList
ComboBox1.AddItem UniqVal 'put each of the collection values in the combo list
Next UniqVal
End Sub
Don't know how you add the districts name in sheet "Data" but the best way is using validation so the districts names are always written the same.
(since you want to make an unique list of them)
Now you've got an unique list its easy to write them down on a sheet.
Could do it in the same loop in which you fill the combobox... |
|
06-05-2003, 03:41 AM
|
|
Newcomer
|
|
Join Date: Jun 2003
Posts: 12
|
|
|
thanks a lot for the help ....
i put the code in "Private Sub Workbook_Open()" so that everytime i open it, it'll populate the combobox ...
but how can i copy the filtered school code, name, and competitors to MMSC sheet ...
need hlp pls ... i'm new on this
|
|
06-05-2003, 04:27 AM
|
 |
Captain TJ
Forum Leader
* Expert *
|
|
Join Date: Jun 2003
Location: England
Posts: 1,664
|
|
Quote:
Originally Posted by kenodahilan
thanks a lot for the help ....
i put the code in "Private Sub Workbook_Open()" so that everytime i open it, it'll populate the combobox ...
but how can i copy the filtered school code, name, and competitors to MMSC sheet ...
need hlp pls ... i'm new on this
This should do what you want:
Code:
Private Sub ComboBox1_Change()
'Turn off screenupdating
Application.ScreenUpdating = False
Dim R As Range
Dim ListRow As Integer
ListRow = 9
'Remove old data from table
While Not IsEmpty(Cells(ListRow, 1))
Range(Cells(ListRow, 1), Cells(ListRow, 3)).ClearContents
ListRow = ListRow + 1
Wend
ListRow = 9
'Loop through data and copy to output table
For Each R In Sheets("Data").Range("C2", Sheets("Data").Range("C2").End(xlDown))
'Have we got a match to the ComboBox
If R.Value = ComboBox1.Value Then
'Put into column 1 the data from 2 cells to the left of the district,etc
Cells(ListRow, 1).Value = R.Offset(0, -2).Value
Cells(ListRow, 2).Value = R.Offset(0, -1).Value
Cells(ListRow, 3).Value = R.Offset(0, 1).Value
'Increase ListRow so we write into the next blank line
ListRow = ListRow + 1
End If
Next R
'Turn screenupdating back on
Application.ScreenUpdating = True
End Sub
|
|
06-05-2003, 04:56 AM
|
|
Newcomer
|
|
Join Date: Jun 2003
Posts: 12
|
|
well .... you are a genius ....
it solved my problem .... the boss will not be angry with me when he comes in 
thanks a lot for the help |
|
06-05-2003, 09:07 AM
|
|
Down...
Retired Moderator
* Expert *
|
|
Join Date: Dec 2002
Location: Belgium.
Posts: 6,731
|
|
Some minor modifications on TinyJack's code :
Code:
Private Sub ComboBox1_Change()
Dim R As Range, ListRow As Integer
Application.ScreenUpdating = False
Range("A9:C25").ClearContents 'Empty the Table on sheet "MMSC"
'avoid using loops as much as possible an this is only 1 line of code
ListRow = 9
'Loop through data and copy to output table
For Each R In Sheets("Data").Range("C2", Sheets("Data").Range("C2").End(xlDown))
'Have we got a match to the ComboBox
If R.Value = ComboBox1.Value Then
'Put into column 1 the data from 2 cells to the left of the district,etc
Cells(ListRow, 1).Value = R.Offset(0, -2).Value
Cells(ListRow, 2).Value = R.Offset(0, -1).Value
Cells(ListRow, 3).Value = R.Offset(0, 1).Value
'Increase ListRow so we write into the next blank line
ListRow = ListRow + 1
End If
Next R
Application.ScreenUpdating = True
End Sub
|
|
06-05-2003, 07:11 PM
|
|
Newcomer
|
|
Join Date: Jun 2003
Posts: 12
|
|
|
good point italkid .... thanks ....
another one ... is there a way to show on the chart only the ones on the list/table?
e.g. if there are 5 schools only 5 bars in the chart and if there are 3 schools only 3 bars will be shown in the chart ...
|
|
06-06-2003, 12:36 AM
|
|
Down...
Retired Moderator
* Expert *
|
|
Join Date: Dec 2002
Location: Belgium.
Posts: 6,731
|
|
|
Hmmm,took a look at your sheet and played around with the schools
(adding and deleting some),and the chart seems to work properly so...?
3 schools = 3 bars,4 schools gives 4 bars etc.
Don't see anything wrong.
|
|
06-06-2003, 04:34 AM
|
|
Newcomer
|
|
Join Date: Jun 2003
Posts: 12
|
|
|
it's working fine i know ... but what if i add new schools ...
e.g. 10 schools for every district ...
is it possible to autolink the chart to whatever number of schools there is on the list?
|
|
06-06-2003, 05:38 AM
|
|
Newcomer
|
|
Join Date: Jun 2003
Posts: 12
|
|
|
additional ....
is it possible not to plot the blank cells?
|
|
06-06-2003, 10:07 AM
|
|
Down...
Retired Moderator
* Expert *
|
|
Join Date: Dec 2002
Location: Belgium.
Posts: 6,731
|
|
Ahhhh,shure...
Sorry i misunderstand your question this morning (for me).
For somebody awake it whas an obvious question but i at that time
came just back from my work (8:30 AM !!).
So i edited the Combobox_Change code and you can implement it as it is
in your project.
Code:
Private Sub ComboBox1_Change()
Dim Ch As Chart
Dim Vr As Range, Xr As Range
Dim R As Range, ListRow As Integer
Application.ScreenUpdating = False
Range("A9:C25").ClearContents 'Empty the Table on sheet "MMSC"
ListRow = 9
'Loop through data and copy to output table
For Each R In Sheets("Data").Range("C2", Sheets("Data").Range("C2").End(xlDown))
'Have we got a match to the ComboBox
If R.Value = ComboBox1.Value Then
'Put into column 1 the data from 2 cells to the left of the district,etc
Cells(ListRow, 1).Value = R.Offset(0, -2).Value
Cells(ListRow, 2).Value = R.Offset(0, -1).Value
Cells(ListRow, 3).Value = R.Offset(0, 1).Value
'Increase ListRow so we write into the next blank line
ListRow = ListRow + 1
End If
Next R
Set Xr = Range("A9", Range("A9").End(xlDown))
Set Vr = Range("C9", Range("C9").End(xlDown))
Set Ch = Sheets("MMSC").ChartObjects(1).Chart
With Ch
.SeriesCollection(1).XValues = Xr
.SeriesCollection(1).Values = Vr
End With
Application.ScreenUpdating = True
Set Xr = Nothing
Set Vr = Nothing
Set Ch = Nothing
End Sub
As long as you don't replace the table on sheet "MMSC" the code will work. |
|
06-06-2003, 09:44 PM
|
|
Newcomer
|
|
Join Date: Jun 2003
Posts: 12
|
|
|
geez ... i just got back to work ...
from the looks of it ....
it's working perfectly now
thanks for all the help on my project .... GOD bless you!
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
|
