Vlookup with Sourcelist in VBA

[BIGMOOSE88]

Is there anyway when doing vlookup through VBA to include the sourcelist (Ranges) in the VBA code itself without having to put any information in the sheet?

I found this code by Benvolio in the Mr Excel Message Board Archive:

Sub Example_of_Vlookup()
Dim lookFor As Range
Dim rng As Range
Dim col As Integer
Dim found As Variant

Set lookFor = Sheets("Sheet2").Range("A1")
Set rng = Sheets("Sheet1").Columns("A:C")
col = 3

On Error Resume Next
found = Application.Vlookup(lookFor.Value, rng, col, 0)
If IsError(found) Then
MsgBox lookFor & " not found"
Else: MsgBox "The look-up value of " & lookFor & " is " & found & " in column " & col
End If
On Error GoTo 0
End Sub

 

[Richard Schollar]

Hi

You can use a Vlookup on an array rather than a range if that's what you mean. You really need to explain in more detail what you are wanting to do though because it wasn't clear to me from your first post.

 

[BIGMOOSE88]

Hi

You can use a Vlookup on an array rather than a range if that's what you mean. You really need to explain in more detail what you are wanting to do though because it wasn't clear to me from your first post.

I dont want any data accessible to the user so I would like the vlookup function to extract the data from the vba code rather than the sheet.

 

[Richard Schollar]

The following will return "cat" demonstrating how you can use Vlookup with an array created wholly in code. I'm not sure how useful this is to be honest if you have a very large dataset though:

Dim arr As Variant

arr = Array(Array("a","Dog"),Array("g","Cat"),Array("p","Horse"))

MsgBox Application.Vlookup("g",arr,2,0)   'returns 'Cat'

 

[BIGMOOSE88]

The following will return "cat" demonstrating how you can use Vlookup with an array created wholly in code. I'm not sure how useful this is to be honest if you have a very large dataset though:

Dim arr As Variant

arr = Array(Array("a","Dog"),Array("g","Cat"),Array("p","Horse"))

MsgBox Application.Vlookup("g",arr,2,0)   'returns 'Cat'

there isnt another way to do vlookup by code and include a range of data to extract from within the code?

 

[Richard Schollar]

I'm sorry, how do you mean? Either you want to use an Excel range or you don't. You can populate an array from a range and then vlookup against this in code:

Dim arr As Variant

arr = Sheets("Some Sheet").Range("A1:G100").Value

MsgBox Application.Vlookup(SomeValue,arr,3,0)  'returns match of SomeValue from col C of original range

 

[BIGMOOSE88]

I dont want to include the range in the worksheet because I dont want my end user to have access to it, I want to physically write the data for the range in the vba code and write out a vlookup function against that (maybe it isnt possible I dont have much experience with excel or vba)

 

[Richard Schollar]

OK now you're confusing me as I have shown you already how to write out an array in VBA and then run a Vlookup against that - does it not do what you want and if not in what way would you want it to work?

 

[BIGMOOSE88]

OK now you're confusing me as I have shown you already how to write out an array in VBA and then run a Vlookup against that - does it not do what you want and if not in what way would you want it to work?

No the array is based on data that is in a range on the worksheet---which is cool and all---but what I needed was to figure out a way to have all the data that I would normally place in a sheet into the vba code

 

[Richard Schollar]

The following will return "cat" demonstrating how you can use Vlookup with an array created wholly in code. I'm not sure how useful this is to be honest if you have a very large dataset though:

Dim arr As Variant

arr = Array(Array("a","Dog"),Array("g","Cat"),Array("p","Horse"))

MsgBox Application.Vlookup("g",arr,2,0)   'returns 'Cat'

The above makes no use of any sheet ranges neither in populating nor using the array created.