Excel Forum

Code: Select all

'ค้นหา
Private Sub btsearch1_Click()
   Dim found As Boolean
   Dim txt As String
   Dim r As Range
    On Error Resume Next
    Dim nRow As String
    For Each r In Sheet6.Columns(1).SpecialCells(xlCellTypeConstants)
    Sheet6.Activate
        If Right(r.Value, 3) = Right(txtsearch1.Text, 3) Then
            nRow = r.row
            found = True
            Exit For
        End If
    Next r
    If found Then
        If Not IsNumeric(VBA.Right(txtsearch1.Text, 3)) Then
            MsgBox "กรุณากรอกข้อมูลเป็นตัวเลข"
            Exit Sub
        End If
        If Err.Number = 91 Then
        ListBox1.RowSource = "txtsearch1.Text"
  TextBox7.Value = ""
    TextBox8.Value = ""
    TextBox9.Value = ""
    TextBox10.Value = ""
    MsgBox "ไม่มีข้อมูล"
    End If
 TextBox7.Value = Cells(nRow, 1)
TextBox8.Value = Cells(nRow, 8)
    TextBox9.Value = Cells(nRow, 2)
    TextBox10.Value = Cells(nRow, 13)
     txt = "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)
    ListBox1.Value = txt
     Exit Sub
    End If
    Sheet1.Activate
End Sub

Code: Select all

    If Right(r.Value, 3) = Right(txtsearch1.Text, 3) Or r.Offset(0, 4).Value = chkDate Then
        nRow = r.Row
        found = True
        Exit For
    End If

Code: Select all

ค้นหา1
Private Sub btsearch1_Click()
   Dim found As Boolean
   Dim txt As String
   Dim r As Range
    Dim chkDate As Date
    On Error Resume Next
    Dim nRow As String
    For Each r In Sheet6.Columns(1).SpecialCells(xlCellTypeConstants)
    Sheet6.Activate
    If Right(r.Value, 3) = Right(txtsearch1.Text, 3) Or r.Offset(0, 4).Value = chkDate Then
        nRow = r.row
        found = True
        Exit For
    End If
       
    Next r
    If found Then
        If Not IsNumeric(VBA.Right(txtsearch1.Text, 3)) Then
            MsgBox "กรุณาใส่ข้อมูลเป็นตัวเลข"
            Exit Sub
        End If
        If Err.Number = 91 Then
        'TextBox1.RowSource = "txtsearch1.Text"
  TextBox7.Value = ""
    TextBox8.Value = ""
    TextBox9.Value = ""
    TextBox10.Value = ""
    'MsgBox "ไม่มีข้อมูล"
    End If
 TextBox7.Value = Cells(nRow, 1)
TextBox8.Value = Cells(nRow, 8)
    TextBox9.Value = Cells(nRow, 2)
    TextBox10.Value = Cells(nRow, 13)
     txt = "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)
    ListBox1.Value = txt
     Exit Sub
     Else
       MsgBox "ไม่มีข้อมูล"
    End If
    Sheet1.Activate
End Sub

Code: Select all

Dim chkDate As Date
    On Error Resume Next
    Dim nRow As String
    For Each r In Sheet6.Columns(1).SpecialCells(xlCellTypeConstants)
    Sheet6.Activate
    chkDate = cmday Or cmmonth Or cmyear
    If Right(r.Value, 3) = Right(txtsearch1.Text, 3) Or r.Offset(0, 4).Value = chkDate Then
        nRow = r.row
        found = True
        Exit For
    End If
    Next r

Code: Select all

Private Sub btsearch1_Click()
   Dim found As Boolean
   Dim txt As String
   Dim r As Range
    Dim chkDate As Date
    On Error Resume Next
    Dim nRow As String
    For Each r In Sheet6.Columns(1).SpecialCells(xlCellTypeConstants)
    Sheet6.Activate
    chkDate = CDate(Applicion.Text(cmday & "/" & cmmonth & "/" & cmyear, "ddmmyyyy"))
    If Right(r.Value, 3) = Right(txtsearch1.Text, 3) Or r.Offset(0, 4).Value = chkDate Then
        nRow = r.row
        found = True
        Exit For
    End If
    Next r
    If found Then
        If Not IsNumeric(VBA.Right(txtsearch1.Text, 3)) Then
            MsgBox "กรุณาใส่ข้อมูลเป็นตัวเลข"
            Exit Sub
        End If
        If Err.Number = 91 Then
        TextBox1.RowSource = "txtsearch1.Text"
  TextBox7.Value = ""
    TextBox8.Value = ""
    TextBox9.Value = ""
    TextBox10.Value = ""
      End If

Code: Select all

    Dim chkDate As Date
    On Error Resume Next
    Dim nRow As String
    
    chkDate = DateSerial(cmyear, cmmonth.ListIndex + 1, cmday)
    Sheet6.Activate
    For Each r In Sheet6.Columns(1).SpecialCells(xlCellTypeConstants)
    If Right(r.Value, 3) = Right(txtsearch1.Text, 3) Or r.Offset(0, 4).Value2 = CLng(chkDate) Then
        nRow = r.Row
        found = True
        Exit For
    End If