Vlookup in excel vba

Re: Vlookup in excel vba

Why would you want VBA to add the formula? Set up the formulas and protection and the formula cannot be deleted.

Re: Vlookup in excel vba

See attached workbook, adopt as per your requirement if this how you wanted.

Option Explicit
Sub Test(dic As Object)
    Dim A As Variant, i As Long
    A = Sheets("Sheet2").Cells(1).CurrentRegion.Value
    For i = 2 To UBound(A, 1)
        If Not dic.exists(A(i, 1)) Then
            dic.Item(A(i, 1)) = VBA.Array(A(i, 2), A(i, 3))
        End If
    Next
End Sub




Private Sub Worksheet_Change(ByVal Target As Range)
    Dim dic As Object
    Application.EnableEvents = False
    If Not Target Is Nothing And Target.Address = Sheets("Sheet1").Range("C3").Address Then
        Set dic = CreateObject("Scripting.dictionary"): Test dic
        If dic.exists(Target.Value) Then
            Range("C4").Value = dic.Item(Target.Value)(0)
            Range("C5").Value = dic.Item(Target.Value)(1)
        End If
    End If
    Application.EnableEvents = True
End Sub

Re: Vlookup in excel vba

That is a real waste of time. All that way ove rcomplicated VBA to do what can be dine more efficiently with a formula is ridiculous!

Re: Vlookup in excel vba

I should point out that the posted code fails on my machine. Its no fault of the coding, just that Excel for Mac doesn't support dictionary objects.
Thats one of the many reasons that native Excel formulas are preferred over VBA.
Another is that changing lay-outs, moving cells, inserting rows etc can be accomplished without the added step of changing hard coded values in VB routines.

Re: Vlookup in excel vba

Another reason is that if the end user does not enable macros then nothing happens.

In short, native excel functions are generally faster because they are most certainly written in C/C++ which produces fast native code.

This code works (not sure about with a Mac) but VLOOKUP would be faster

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rCl As Range
    With Target
        If .Count = 1 And .Address = "$C$3" Then
            Set rCl = Sheet2.Range("A1").CurrentRegion.Columns(1).Find(.Text)
            If Not rCl Is Nothing Then
                .Offset(1).Value = rCl.Offset(, 1).Value
                .Offset(2).Value = rCl.Offset(, 2).Value
            Else: MsgBox .Value & " cannot be found", vbCritical, "ID missing"
            End If
        End If
    End With
End Sub

Re: Vlookup in excel vba

Find works fine on a Mac, for event code. For a UDF, it gives #VALUE error when called by a worksheet formula, just like .NavigateArrows or .SpecialCells does.

Re: Vlookup in excel vba

Thanks Mike