Snasui Excel Forum

Code: Select all

 Dim sdsheet As Worksheet, ersheet As Worksheet
Dim sdLR As Long, erLR As Long, y As Long, N As Long
Dim myfind As Object
Set myfind = Sheets("ยืม-คืน").Range("F2:F1000").Find(cb_รหัส.Value)
If myfind Is Nothing Then
MsgBox "ไม่มีรายการวัสดุ"
Else
Set sdsheet = ThisWorkbook.Sheets("ยืม-คืน")
    If sdsheet.Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
    sdLR = 2
    Else
    sdLR = Application.WorksheetFunction.Max(5, sdsheet.Cells(Rows.Count, 1).End(xlUp).Row)
    End If
sdsheet.Select
   ListBox1.Clear
    'For x = 2 To sdLR
      For N = 2 To Module1.EndRow(Sheets("ยืม-คืน"), "C")
     With Sheets("ยืม-คืน").Range("C" & N)
    'If sdsheet.Cells(x, 3) >= TextBox1.Text And sdsheet.Cells(x, 3) <= TextBox2.Text Then
If DateSerial(Year(.Value), Month(.Value), Day(.Value)) >= DateSerial(ComboBox3.Text, ComboBox2.Text, ComboBox1.Text) And _
                DateSerial(Year(.Value), Month(.Value), Day(.Value)) <= DateSerial(ComboBox6.Text, ComboBox5.Text, ComboBox4.Text) Then
With ListBox1
                   .ColumnCount = 9
                ListBox1.AddItem CStr(sdsheet.Cells(N, 1))
                ListBox1.List(ListBox1.ListCount - 1, 1) = CStr(sdsheet.Cells(N, 2))
                ListBox1.List(ListBox1.ListCount - 1, 2) = CStr(sdsheet.Cells(N, 3))
                ListBox1.List(ListBox1.ListCount - 1, 3) = Format(sdsheet.Cells(N, 4), "h:mm")
                ListBox1.List(ListBox1.ListCount - 1, 4) = CStr(sdsheet.Cells(N, 5))
                ListBox1.List(ListBox1.ListCount - 1, 5) = CStr(sdsheet.Cells(N, 6))
                ListBox1.List(ListBox1.ListCount - 1, 6) = CStr(sdsheet.Cells(N, 7))
                ListBox1.List(ListBox1.ListCount - 1, 7) = CStr(sdsheet.Cells(N, 8))
                ListBox1.List(ListBox1.ListCount - 1, 8) = CStr(sdsheet.Cells(N, 9))
              End With
            End If
            End With
            Next N
End If
End Sub