Excel Forum

Code: Select all

Private Sub cbx1_Change()
On Error Resume Next
If IsNumeric(Me.cbx1) Then
With Application.WorksheetFunction
        txb3.Value = .Index(Worksheets("datalist").Range("E3:E18"), .Match(CInt(Me.cbx1), Worksheets("datalist").Range("D3:D18"), 0))
        txb1.Value = .Index(Worksheets("datalist").Range("B3:B18"), .Match(CInt(Me.cbx1), Worksheets("datalist").Range("D3:D18"), 0))
End With
Else
With Application.WorksheetFunction
        txb3.Value = .Index(Worksheets("datalist").Range("E3:E18"), .Match(Me.cbx1, Worksheets("datalist").Range("D3:D18"), 0))
        txb1.Value = .Index(Worksheets("datalist").Range("B3:B18"), .Match(Me.cbx1, Worksheets("datalist").Range("D3:D18"), 0))
End With
End If
If Err <> 0 Then
MsgBox "Please check your code."
End If
End Sub

Code: Select all

Private Sub cbx1_Change()
    Dim r As Range, rall As Range
    On Error Resume Next
    With Sheets("datalist")
        Set rall = .Range("d3", .Range("d" & .Rows.Count).End(xlUp))
        For Each r In rall
            If Application.IsText(r.Value) Then
                If r.Value = cbx1.Text Then
                    txb3.Text = r.Offset(0, 1).Value
                    Exit For
                End If
            Else
                If r.Value = CLng(cbx1.Text) Then
                    txb3.Text = r.Offset(0, 1).Value
                    Exit For
                End If
            End If
        Next r
    End With
End Sub