Excel Forum

Code: Select all

Public Sub searchDataMode_mode()
    
    On Error GoTo ExitSub
        
'กำลังจะเอามาแก้

Dim Data_search As String
Dim search_row As Single
Dim EmpID_no As String

Dim i As Integer
Dim c As Integer

Const Form_sheet = "Form"
Const DataReport_sheet = "DataReport"

Const q_no_col = 1
Const Title_col = 2

Const EngN_col = 3
Const ThaN_col = 4
Const DocNum_col = 12
Const Typ1_col = 14
Const DD_col = 15
Const Item_col = 19
Const Typ2_col = 21
Const Year_col = 24

'************************************************************************************************

Data_search = Worksheets(Form_sheet).id_txtbox.Value

search_row = 2
EmpID_no = Worksheets(DataReport_sheet).Cells(search_row, q_no_col).Value

    Do Until quotation_no = Data_search
    
        search_row = search_row + 1
        quotation_no = Worksheets(DataReport_sheet).Cells(search_row, q_no_col).Value
        
    Loop
    
With Worksheets(Form_sheet)
    
    .Range("n12").Value = Worksheets(DataReport_sheet).Cells(search_row, q_no_col).Value
    .Range("o12").Value = Worksheets(DataReport_sheet).Cells(search_row, Title_col).Value
    .Range("p12").Value = Worksheets(DataReport_sheet).Cells(search_row, EngN_col).Value
    
    .Range("q12").Value = Worksheets(DataReport_sheet).Cells(search_row, ThaN_col).Value
    .Range("r12").Value = Worksheets(DataReport_sheet).Cells(search_row, DocNum_col).Value
     
     .Range("s12").Value = Worksheets(DataReport_sheet).Cells(search_row, Typ1_col).Value
     .Range("t12").Value = Worksheets(DataReport_sheet).Cells(search_row, DD_col).Value
     .Range("u12").Value = Worksheets(DataReport_sheet).Cells(search_row, Item_col).Value
     .Range("v12").Value = Worksheets(DataReport_sheet).Cells(search_row, Typ2_col).Value
     .Range("w12").Value = Worksheets(DataReport_sheet).Cells(search_row, Year_col).Value
     
End With

Exit Sub
ExitSub:
    MsgBox "Database Not Found", vbInformation, "Search Program"
    
'Call ClearTextbox

End Sub

Code: Select all

Sub test()
Dim lr As Long, r As Range
Dim Data_search As String
Range("N12:X31").ClearContents
Data_search = Worksheets("Form").id_txtbox.Value
With Sheets("DataReport")
    lr = .Range("A" & Rows.Count).End(xlUp).Row
    For Each r In .Range("A2:A" & lr)
        If r.Value = Data_search Then
            Sheets("Form").Range("N" & Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 4).Value = r.Resize(1, 4).Value
            Sheets("Form").Range("R" & Rows.Count).End(xlUp).Offset(1, 0).Value = r.Offset(0, 11).Value
            Sheets("Form").Range("S" & Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 2).Value = r.Offset(0, 13).Resize(1, 2).Value
            Sheets("Form").Range("U" & Rows.Count).End(xlUp).Offset(1, 0).Value = r.Offset(0, 18).Value
            Sheets("Form").Range("V" & Rows.Count).End(xlUp).Offset(1, 0).Value = r.Offset(0, 20).Value
            Sheets("Form").Range("W" & Rows.Count).End(xlUp).Offset(1, 0).Value = r.Offset(0, 23).Value
        End If
    Next r
End With
End Sub