maghari
02-06-2020, 01:51 AM
hello
i have data in sheet1 from a2:g1000 and userform contains combobox1 and dtpicker1,2 and list box when i choose from combobox the client and choose the date from dtpicker1,2 it supposing show the data in listbox but it gives me error "object required" the yellow highliht in this line Set r = sh.Range("G2:G" & sh.Range("G" & Rows.Count).End(xlUp).Row)
my code :
Option Explicit
Dim sh As Worksheet
Private Sub ComboBox1_Change()
Dim f As Range, r As Range, cell As String
Dim dtp1 As Date, dtp2 As Date
ListBox1.Clear
If ComboBox1.Value = "" Or ComboBox1.ListIndex = -1 Then
Exit Sub
Set r = sh.Range("G2:G" & sh.Range("G" & Rows.Count).End(xlUp).Row)
Set f = r.Find(ComboBox1.Value, , xlValues, xlWhole)
If Not f Is Nothing Then
cell = f.Address
Do
If DTPicker1.Value = "" Or DTPicker2.Value = "" Then
dtp1 = sh.Range("C" & f.Row).Value
dtp2 = sh.Range("C" & f.Row).Value
Else
dtp1 = DTPicker1.Value dtp2 = DTPicker2.Value
End If
If sh.Range("C" & f.Row).Value >= dtp1 And sh.Range("C" & f.Row).Value <= dtp2 Then
With ListBox1
.AddItem sh.Range("A" & f.Row).Value
.List(.ListCount - 1, 1) = sh.Range("B" & f.Row).Value 'Data from Col B
.List(.ListCount - 1, 2) = sh.Range("C" & f.Row).Value 'Data from Col C
.List(.ListCount - 1, 3) = sh.Range("D" & f.Row).Value 'Data from Col D
.List(.ListCount - 1, 4) = sh.Range("E" & f.Row).Value 'Data from Col E
.List(.ListCount - 1, 5) = sh.Range("F" & f.Row).Value 'Data from Col f
.List(.ListCount - 1, 6) = sh.Range("G" & f.Row).Value 'Data from Col g
End With
End If
Set f = r.FindNext(f)
Loop While Not f Is Nothing And f.Address <> cell
End If
End Sub
Private Sub UserForm_Initialize()
Dim i As Long, dic As Object
Set sh = sheet1
Set dic = CreateObject("Scripting.Dictionary")
For i = 2 To sh.Range("G" & Rows.Count).End(xlUp).Row
dic(sh.Range("G" & i).Value) = Empty
Next
ComboBox1.List = dic.keys
ListBox1.ColumnCount = 7
End Sub
i have data in sheet1 from a2:g1000 and userform contains combobox1 and dtpicker1,2 and list box when i choose from combobox the client and choose the date from dtpicker1,2 it supposing show the data in listbox but it gives me error "object required" the yellow highliht in this line Set r = sh.Range("G2:G" & sh.Range("G" & Rows.Count).End(xlUp).Row)
my code :
Option Explicit
Dim sh As Worksheet
Private Sub ComboBox1_Change()
Dim f As Range, r As Range, cell As String
Dim dtp1 As Date, dtp2 As Date
ListBox1.Clear
If ComboBox1.Value = "" Or ComboBox1.ListIndex = -1 Then
Exit Sub
Set r = sh.Range("G2:G" & sh.Range("G" & Rows.Count).End(xlUp).Row)
Set f = r.Find(ComboBox1.Value, , xlValues, xlWhole)
If Not f Is Nothing Then
cell = f.Address
Do
If DTPicker1.Value = "" Or DTPicker2.Value = "" Then
dtp1 = sh.Range("C" & f.Row).Value
dtp2 = sh.Range("C" & f.Row).Value
Else
dtp1 = DTPicker1.Value dtp2 = DTPicker2.Value
End If
If sh.Range("C" & f.Row).Value >= dtp1 And sh.Range("C" & f.Row).Value <= dtp2 Then
With ListBox1
.AddItem sh.Range("A" & f.Row).Value
.List(.ListCount - 1, 1) = sh.Range("B" & f.Row).Value 'Data from Col B
.List(.ListCount - 1, 2) = sh.Range("C" & f.Row).Value 'Data from Col C
.List(.ListCount - 1, 3) = sh.Range("D" & f.Row).Value 'Data from Col D
.List(.ListCount - 1, 4) = sh.Range("E" & f.Row).Value 'Data from Col E
.List(.ListCount - 1, 5) = sh.Range("F" & f.Row).Value 'Data from Col f
.List(.ListCount - 1, 6) = sh.Range("G" & f.Row).Value 'Data from Col g
End With
End If
Set f = r.FindNext(f)
Loop While Not f Is Nothing And f.Address <> cell
End If
End Sub
Private Sub UserForm_Initialize()
Dim i As Long, dic As Object
Set sh = sheet1
Set dic = CreateObject("Scripting.Dictionary")
For i = 2 To sh.Range("G" & Rows.Count).End(xlUp).Row
dic(sh.Range("G" & i).Value) = Empty
Next
ComboBox1.List = dic.keys
ListBox1.ColumnCount = 7
End Sub