Snasui Excel Forum

Code: Select all

Public Sub search()
    Dim input1 As Range, iRow As Long
    Dim tempRow As Long, cell As Range
    Set input1 = Worksheets(1).Range("b1")
    Worksheets(1).Range("a2").Resize(1000, 10).ClearContents
    For Each cell In Worksheets(2).UsedRange.Offset(1, 0)
        If cell.Value = input1.Value Then
            iRow = cell.row
            If iRow <> tempRow Then
                Worksheets(2).Cells(cell.row, 1).Resize(1, 7).Copy
                With Worksheets(1)
                    .Range("a" & .Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
                End With
            End If
            tempRow = iRow
        End If
    Next cell
End Sub

Code: Select all

Sub test()
Dim input1 As Range
Dim rcell As Range
Dim lr As Long
Set input1 = Sheets("Finddata").Range("B1")
lr = Range("A" & Rows.Count).End(xlUp).row
If lr > 2 Then
    Sheets("Finddata").Range("A3", Range("A" & Rows.Count).End(xlUp)).Resize(, 5).ClearContents
End If
For Each rcell In Sheets("Database").Range("A8", Sheets("Database").Range("A" & Rows.Count).End(xlUp))
    If rcell.Value Like "*" & input1.Value & "*" Then
        rcell.Resize(1, 5).Copy
        With Sheets("Finddata")
            .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        End With
    End If
Next rcell
Application.CutCopyMode = False
End Sub

Code: Select all

Private Sub CommandButton1_Click() ' ¤é¹ËҢͧ userform1
    On Error Resume Next
    Set myRange = Worksheets(2).Range("A:H")
    TextBox2.Value = Application.WorksheetFunction.VLookup(TextBox1.Text, myRange, 1, False)
    TextBox3.Value = Application.WorksheetFunction.VLookup(TextBox1.Text, myRange, 2, False)
    TextBox4.Value = Application.WorksheetFunction.VLookup(TextBox1.Text, myRange, 3, False)
    'Other code
End Sub

Code: Select all

Private Sub CommandButton2_Click() ' บันทึกข้อมูลหลังจากแก้ไข
    Dim recdRow As Long
    recdRow = Application.Match(TextBox1.Text, Sheets("Database").Range("a:a"), 0)
    With Sheets("database")
        .Cells(recdRow, 2).Value = TextBox2.Text
        'Other code
        .Cells(recdRow, 7).Value = TextBox10.Text
    End With
End Sub