Excel Forum

Code: Select all

Private Sub CommandButton1_Click()
TextBox2.Value = WorksheetFunction.Index(Worksheets("Sheet1").Range("A:E"), WorksheetFunction.Match(TextBox1.Value, Worksheets("Sheet1").Range("A:A"), 0), 5)
End Sub

Code: Select all

Private Sub CommandButton1_Click()
    Dim sh As Worksheet
    Set sh = Sheets("Sheet1")
    With WorksheetFunction
        If .CountIf(sh.Range("A:A"), CInt(0 & TextBox1.Value)) Then
            TextBox2.Value = .Index(sh.Range("A:E"), _
                .Match(CInt(TextBox1.Value), sh.Range("A:A"), 0), 5)
        Else
            TextBox2.Value = ""
            MsgBox "Not found."
        End If
    End With
End Sub