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]

The search column must be text. In my example, look at column A are numbers but captured as text.


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >TEXT</td><td >MESSAGE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">2</td><td >hi</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">3</td><td >bye</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">4</td><td >good</td></tr></table>

Sub test()
  Dim a(), b As String, c As Variant
  a = Range("A2:B3")
  b = "3"
  c = [COLOR=#0000ff]WorksheetFunction[/COLOR].VLookup(b, a, 2, 0)
End Sub

If the value does not exist in column A, then the macro stop, to continue you can use Application:

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

 

[tiredofit]

Thanks but your first example gives me an error!

Furthermore, can the range be a collection?

 

[Fluff]

can the range be a collection?

Not that I'm aware of, it needs to be a range or an array.
You could also use a dictionary and get rid of the vlookup completely

 

[tiredofit]

Thanks

 

[Fluff]

For reference, with a dictionary it would be something like

Sub tiredofit()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2:A12")
         .Item(Cl.Value) = Cl.Offset(, 1).Value
      Next Cl
      Debug.Print .Item("Kent")
   End With
End Sub

EDIT:
Forgot to add sample data

Book1
	A	B
1	County	Ward
2	East Riding of Yorkshire	Kings Park
3	Cambridgeshire	Roman Bank
4	Kent	Walland & Denge Marsh
5	Dorset	Poole Town
6	Greater London	Thamesmead Moorings
7	Devon	Teignmouth East
8	East Sussex	Devonshire
9	South Yorkshire	Thorne & Moorends
10	Suffolk	Westgate
11	Norfolk	Central and Northgate
12	Lincolnshire	Pinchbeck and Surfleet
Sheet1

 

[DanteAmor]

Thanks but your first example gives me an error!

Furthermore, can the range be a collection?

What does the error say?


Remember that you must capture the numbers as text.


Or maybe I should put texts so that there is no confusion, try the following example:


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >TEXT</td><td >MESSAGE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >x</td><td >hi</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >y</td><td >bye</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >z</td><td >good</td></tr></table>

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

 

[tiredofit]

What does the error say?

Your last example worked but the previous one showed:

Error 2042

when I hovered the mouse over c.

 

[tiredofit]

For reference, with a dictionary it would be something like

Thanks, I'll be using this going forwards!

 

[Fluff]

You're welcome & thanks for the feedback.

Also please do not quote entire posts as it just clutters up the thread. Thanks