Doing a VLOOKUP in VBA

[JoeS01]

Can anyone please advise a simple (but clever) lines of code for replicating the VLOOKUP function in VBA, where you have a 2 dimensional array, 2 columns wide and n rows long, and for a given value in one column of the array, find the corresponding number in the other column ?

 

[Domenic]

Assuming that Column A and Column B contain the lookup table, and D1 contains the lookup value, try something like this...

[font=Verdana][color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Range("E1").Value = Application.VLookup(Range("D1").Value, Range("A1:B" & LastRow), 2, 0)
        
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[diddi]

you can use worksheetfunction, or do you not want to use vlookup at all?

 

[JoeS01]

thanks Domenic, much appreciated, but I would prefer not to use the internal Excel VLOOKUP function for the application I had in mind

 

[Domenic]

Any reason why you don't want to use VLOOKUP? In any case, based on the same earlier assumptions, try something like this...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] FoundCell [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    [color=darkblue]With[/color] Range("A1:A" & LastRow)
        [color=darkblue]Set[/color] FoundCell = .Find(Range("D1").Value, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
        [color=darkblue]If[/color] [color=darkblue]Not[/color] FoundCell [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
            Range("E1").Value = Cells(FoundCell.Row, "B").Value
            [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    MsgBox "The lookup value was not found...", vbExclamation
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[JoeS01]

Thank you Domenic, there are many lessons for me to learn in this code. I really appreciate it.

 

[Domenic]

You're very welcome! Thanks for the feedback!