PDA

View Full Version : Filter Data in Sheet to ListView



marreco
03-06-2012, 05:26 PM
Good night!
I'm trying to make an adjustment, and I can not.
In my formmulario I select the initial hours (08:00) ComboBox2 in the final hours (10:00) in ComboBox3.
After I select the user in ComboBox1.

After that press the button CommandButton1 (Search).

These data will be released in the ListView.

Something is going wrong and I do not know what it is.:dunno

Can anyone help me?

Bob Phillips
03-06-2012, 06:19 PM
Sub Get_Stuff()
Dim LR As Long
Dim sh As Worksheet
Dim Rng As Range
Dim Cel As Range
Dim LC As Long
Dim x As Long
Dim y As Long

Set sh = Sheets("Agendamento")

With sh
LR = .Cells.Find("*", .Cells(Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
.Range("B7").AutoFilter
.Range("B7:G" & LR).AutoFilter Field:=1, Criteria1:= _
">=" & CDate(FrmFind.ComboBox2.Value), Operator:=xlAnd, Criteria2:="<=" & CDate(FrmFind.ComboBox3.Value)
End With

Set Rng = sh.AutoFilter.Range

x = Rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1

If x >= 1 Then
FrmFind.ListView1.ListItems.Clear

With sh
LC = .Cells(7, .Range("B7").SpecialCells(xlCellTypeLastCell).Column + 1).End(xlToLeft).Column
Set Rng = .Range(.Cells(7, 2), .Cells(LR, 2)).SpecialCells(xlCellTypeVisible)
For Each Cel In Rng
With FrmFind.ListView1
.ListItems.Add , , Cel 'add the first value of the row (column 1), to create index
For y = 2 To LC 'add the others values of this row (columns 2 to last column)
.ListItems(.ListItems.Count).ListSubItems.Add , , sh.Cells(Cel.Row, y)
Next
End With
Next
.ShowAllData
End With
Else
MsgBox "Não foi encontrado no Agendamento " & FrmFind.ComboBox1.Value
sh.ShowAllData
End If
End Sub

marreco
03-06-2012, 06:51 PM
Hi, Thank you :friends:

It was very good but need to hit the field "Time", it should appear Hours format.

See the picture.

Thank you

marreco
03-07-2012, 10:07 AM
Good afternoon!

It is missing only the details of the llistview hours to complete my project.:help

I would be very grateful for the help!:*)

Thank you!

marreco
03-07-2012, 11:58 AM
hi.
Im solved

http://www.excelforum.com/excel-programming/818356-adaptation-hours-using-combobox.html

hi XLD Thank you :hi:

Bob Phillips
03-07-2012, 03:17 PM
Oh dear, anther cross-poster, another for the bin list.