Excel VBA Lookup

Question

Trying to use VBA in Excel to perform some VLookup equivalent function. I have this sample table:

Basically, this is what I want to do:

1) Based on the value in B12, lookup the value in table A1:A8. 2) Set cell B13 with the lookup returned value 3) If no match found (example, B12=100000), throw an error message.

I am currently using a bunch of "IF.. ElseIf" statements and it is becoming too cumbersome to maintain.

Thanks in advance.

Your qtys are in ascending order. what's wrong with vlookup? — user4039065
I would use a formula here, especially if it's such simple logic, as opposed to writing VBA adding complexity to the workbook where none is needed. — JNevill
@Jeeped - For certain reason, I don't want to use a vlookup formula in B13. Instead, I rather use a VBA that does the lookup and set the value B13. — Ultra GC

Unknown Unknown · Accepted Answer · 2018-09-12T21:30:11

Try this in the worksheet's private code sheet (right-click worksheet name tab, View Code.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "B12" Then
        On Error GoTo meh
        Application.EnableEvents = False
        Dim m As Variant
        If IsNumeric(Target) Then
            If Target < 1 Or Target > Application.Max(Range("A2:A8")) Then
                Target.Offset(1, 0) = "out of range"
            Else
                m = Application.Match(Target, Range("A1:A8"))
                Target.Offset(1, 0) = Cells(m, "B").Value
            End If
        End If
    End If

meh:
    Application.EnableEvents = True

End Sub

Excel VBA Lookup

2 Answers