Snasui Excel Forum

Code: Select all

TextBox5 = Format(TextBox5, "dd/mm/yyyy")

Code: Select all

Private Sub CommandButton10_Click() 
Dim dte As Date

On Error Resume Next
Err.Clear
If TextBox1.Value <> "" Then
        txtbox_No = 1
       Set nRow = Sheet1.Columns(1).Find(TextBox1.Text)
ElseIf TextBox2.Value <> "" Then
        txtbox_No = 2
        Set nRow = Sheet1.Columns(2).Find(TextBox2.Text)
ElseIf TextBox3.Value <> "" Then
        txtbox_No = 3
        Set nRow = Sheet1.Columns(3).Find(TextBox3.Text)
ElseIf TextBox4.Value <> "" Then
        txtbox_No = 4
        Set nRow = Sheet1.Columns(4).Find(TextBox4.Text)
ElseIf TextBox5.Value <> "" Then
 mbox = TextBox5.Text

 If IsDate(mbox) Then
     'dte = CDate(mbox)
     Set nRow = Sheet1.Columns(5).Find(TextBox5.Text)
  Else
     MsgBox "ข้อมูลไม่ตรงกับรหัสของวันที่"
  End If
     'MsgBox "Date Search Not Now Active Please Use Other Function"
     'GoTo nNext
ElseIf TextBox7.Value <> "" Then
        txtbox_No = 7
        Set nRow = Sheet1.Columns(7).Find(TextBox7.Text)
ElseIf TextBox8.Value <> "" Then
        txtbox_No = 8
        Set nRow = Sheet1.Columns(6).Find(TextBox8.Value)
End If

If Err.Number = 91 Then
    TextBox2.Value = "NotFound DATA"
    TextBox3.Value = "NotFound DATA"
    TextBox4.Value = "NotFound DATA"
    TextBox5.Value = "NotFound DATA"
    TextBox6.Value = "NotFound DATA"
    TextBox7.Value = "NotFound DATA"
    TextBox8.Value = "NotFound DATA"
    TextBox9.Value = "NotFound DATA"
        MsgBox "Don't Have this Word in Record"
    GoTo nNext
End If

TextBox1.Value = Cells(nRow.Row, 1)
TextBox2.Value = Cells(nRow.Row, 2)
TextBox3.Value = Cells(nRow.Row, 3)
TextBox4.Value = Cells(nRow.Row, 4)
TextBox5.Value = Cells(nRow.Row, 5)
TextBox7.Value = Cells(nRow.Row, 7)
TextBox8.Value = Cells(nRow.Row, 6)
TextBox9.Value = Cells(nRow.Row, 8)

Cells(nRow.Row, 1).Activate
nNext:
End Sub

Code: Select all

Dim Rng as Range, strDte As Variant
strDte = Split(TextBox5.Text, "/")

For Each Rng In Sheet1.Columns(5).SpecialCells(xlCellTypeConstants, 1)
    If Rng.Value2 = CLng(DateSerial(strDte(2), strDte(1), strDte(0))) Then
        Set nRow = Rng
        Exit For
    End If
Next Rng