Snasui Excel Forum

Code: Select all

Private Sub TextBox5_AfterUpdate()
    If Me.TextBox5.Text = "" Then Exit Sub
    If WorksheetFunction.CountIf(Sheets("Data").Range("A:A"), Me.TextBox5.Value) = 0 Then
        'Me.TextBox5.Value = ""
        MsgBox "Not found."
        Exit Sub
    End If
    emptyrow = WorksheetFunction.CountA(Range("A:A")) + 1
    
    With Me
        .TextBox6 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox5), Sheets("Data").Range("lookupdata"), 2, 0)
        .TextBox7 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox5), Sheets("Data").Range("lookupdata"), 3, 0)
        .TextBox8 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox5), Sheets("Data").Range("lookupdata"), 4, 0)
    End With
    With Worksheets("IN")
        .Cells(emptyrow, 1).Value = TextBox1.Value
        .Cells(emptyrow, 2).Value = TextBox2.Value
        .Cells(emptyrow, 3).Value = TextBox4.Value
        .Cells(emptyrow, 4).Value = ComboBox1.Value
        .Cells(emptyrow, 5).Value = TextBox5.Value
        .Cells(emptyrow, 6).Value = TextBox6.Value
        .Cells(emptyrow, 7).Value = TextBox7.Value
        .Cells(emptyrow, 8).Value = TextBox8.Value
        .Cells(emptyrow, 9).Value = TextBox9.Value
    End With
End Sub