Vlookup range as a collection

[tiredofit]

In a vlookup, can the range be a collection?

Say I have only two values in cells A1 and A2.

If the range is an array, it works:

    Dim MyArray() As Variant
    
    MyArray() = Cells(1, 1).CurrentRegion.Value
    
    Dim g As Integer
    
    g = 2
    
    Dim j As Variant
    
    j = Application.VLookup(g, MyArray(), 1, False)

but I tried changing from an array to a collection and got an error:

    Dim MyColl As Collection
    Set MyColl = New Collection
    
    Dim i As Integer
    
    For i = 1 To 2
    
        MyColl.Add Cells(i, 1).Value
        
    Next i
    
    Dim g As Integer
    
    g = 2
    
    Dim j As Variant
    
    j = Application.VLookup(g, MyColl, 1, False)

The error message was:

Application-defined or object-defined error

Have I made a mistake with the syntax or can't the range in a vlookup be a collection?

Thanks

_{^{<strike>
</strike>}}

 

[DanteAmor]

Your last example worked but the previous one showed:

Error 2042

when I hovered the mouse over c.

The test is successful, the 5 does not exist in the data, that is why error returns. It is for you to observe the difference between Application and WorksheetFunction.

Sub test()
  Dim a(), b As String, c As Variant
  a = Range("A2:B3")
[COLOR=#ff0000][B]  b = "5"[/B][/COLOR]
  c = Application.VLookup(b, a, 2, 0)
End Sub

 

[tiredofit]

But in your first example, ie this one:

Sub test()
  Dim a(), b As String, c As Variant
  a = Range("A2:B3")
  b = "3"
  c = WorksheetFunction.VLookup(b, a, 2, 0)

End Sub


​

​

the "3" does exist but it still returns an error.

 

[DanteAmor]

But in your first example, ie this one:

Sub test()
  Dim a(), b As String, c As Variant
  a = Range("A2:B3")
  b = "3"
  c = WorksheetFunction.VLookup(b, a, 2, 0)
End Sub
​

​

the "3" does exist but it still returns an error.

But you must put 3 as text in the cell.
Or try the post with my example in post #7

 

[tiredofit]

Thanks

 

[DanteAmor]

Youre welcome.