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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.