Excel Forum

Code: Select all

Private Sub CommandButton1_Click()

    Dim RecordRow As Long
    Dim RecordRange As Range

    On Error Resume Next
        
        RecordRow = Application.Match(CLng(TextBox1.Value), Range("Table1[id]"), 0)

        Set RecordRange = Range("Table1").Cells(1, 1).Offset(RecordRow - 1, 0)

        If Err.Number <> 0 Then
    
            ErrorLabel.Visible = True
            On Error GoTo 0
            Exit Sub
        
        End If

    On Error GoTo 0
    ErrorLabel.Visible = False
    TextBox2.Value = RecordRange(1, 1).Offset(0, 1).Value

End Sub

Code: Select all

Private Sub CommandButton1_Click()

    Dim RecordRow As Long, mText As Long, mNum As Long
    Dim RecordRange As Range
   
    ErrorLabel.Visible = False
    On Error Resume Next
        
    mText = Application.Match(TextBox1.Value, Range("Table1[id]"), 0)
    mNum = Application.Match(CLng(TextBox1.Value), Range("Table1[id]"), 0)
    RecordRow = Application.Max(mText, mNum)
    TextBox2.Text = ActiveSheet.Cells(RecordRow, "b").Value
    If RecordRow = 0 Then
        ErrorLabel.Visible = True
    End If
    
'    Set RecordRange = Range("Table1").Cells(1, 1).Offset(RecordRow - 1, 0)
'    If Err.Number <> 0 Then
'
'        ErrorLabel.Visible = True
'        On Error GoTo 0
'        Exit Sub
'
'    End If
'
    On Error GoTo 0
    'ErrorLabel.Visible = False
'    TextBox2.Value = RecordRange(1, 1).Offset(0, 1).Value

End Sub