Snasui Excel Forum

Code: Select all

 If txtsearch.Text = "" Or Commonth.Text = "" Then
MsgBox "Please fill the complete data"
        Exit Sub
        End If
 Dim found As Boolean
   Dim txt As String
   Dim r As Range
    Dim nRow As String
    Dim rall As Range
    'For Each r In Sheet9.Columns(1).SpecialCells(xlCellTypeConstants)
    Sheet9.Activate
  For Each r In Worksheets("การเบิก").Range("A1:A10000").Cells
If Right(r.Value, 3) = Right(txtsearch.Text, 3) And _
    Commonth.Text = Application.Text(r.Offset(0, 4).Value, "[$- ]mmmm") Then
    nRow = r.Row
    found = True
    Exit For
End If
    Next r
    If found Then
        If Not IsNumeric(VBA.Right(txtsearch.Text, 3)) Then
            MsgBox "Please enter only numeric data"
            Exit Sub
        End If
        'If Err.Number = 91 Then
        'TextBox1.RowSource = "txtsearch.Text & combobox1.value"
   ' End If
    With Sheets("การเบิก")
        Set r = .Rows(1).Find(txt)
        Set rall = .Range(r.Offset(1, 0), .Cells(.Rows.Count, r.Column).End(xlUp)) _
            .SpecialCells(xlCellTypeConstants)
    End With
   txt = "Emp_ID : " & Cells(nRow, 1) & vbCrLf & _
    "Name : " & Cells(nRow, 2) & vbCrLf & _
    "Section : " & Cells(nRow, 3) & vbCrLf & _
    "Uniform_No : " & Cells(nRow, 4) & vbCrLf & vbCrLf & _
    "Date : " & Cells(nRow, 5) & vbCrLf & _
    "Discription : " & Cells(nRow, 6) & vbCrLf & _
    "Reason : " & Cells(nRow, 7) & vbCrLf & _
     "Status : " & Cells(nRow, 8)
    TextBox1.Value = txt
    Exit Sub
    Else
      MsgBox "Data not found !"
    End If

Code: Select all

Dim txt As String, r As Range
Dim nRow As String, rall As Range, i As Integer

If txtsearch.Text = "" Or Commonth.Text = "" Then
    MsgBox "Please fill the complete data"
    Exit Sub
End If

If Not IsNumeric(VBA.Right(txtsearch.Text, 3)) Then
    MsgBox "Please enter only numeric data"
    Exit Sub
End If

With Sheets("การเบิก")
    Set rall = .Range("a3", .Range("a" & .Rows.Count).End(xlUp)) _
        .SpecialCells(xlCellTypeConstants)
End With
txt = ""
For Each r In rall
    If Right(r.Value, 3) = Right(txtsearch.Text, 3) And _
        Commonth.Text = Application.Text(r.Offset(0, 4).Value, "[$- ]mmmm") Then
        nRow = r.Row
        i = i + 1
        txt = "รายการที่ " & i & vbCrLf & _
            "Emp_ID : " & Cells(nRow, 1) & vbCrLf & _
            "Name : " & Cells(nRow, 2) & vbCrLf & _
            "Section : " & Cells(nRow, 3) & vbCrLf & _
            "Uniform_No : " & Cells(nRow, 4) & vbCrLf & _
            "Date : " & Cells(nRow, 5) & vbCrLf & _
            "Discription : " & Cells(nRow, 6) & vbCrLf & _
            "Reason : " & Cells(nRow, 7) & vbCrLf & _
            "Status : " & Cells(nRow, 8)
        TextBox1.Value = IIf(TextBox1.Value <> "", TextBox1.Value & vbCrLf & vbCrLf & txt, txt)
    End If
Next r
If Len(txt) = 0 Then
    MsgBox "Data not found!"
End If