Using VLOOKUP in an Excel 2007 VBA Function

Question

I have tried hours and hours of examples but I continually get the same error of #NAME?

I need to use VLOOKUP within a VBA function and then work with the result before putting the result to a cell. Firstly I am having a problem just to get the VBA VLOOKUP part working.

For testing here is the details Spreadsheet is...

Worksheet = Sheet1

4 rows x 2 columns of data. Cells D1:E4 Dance : 23 French : 42 English : 2 Music : 33

In cell A1 I will have a user imputed content that will a value from column D eg French

In Cell B1 I will call the function =GetQty(A1,D1:E4,2) but I always get #NAME?

1) How do I call the function properly. Am I doing this corretly?

In my VBA function "GetQty" I just want to do the VLOOKUP on a value of cell A1 and return the value from the matching entry from the table(Cell D1:E4), eg return 42 and then place that value into column B1 and (also column C1 via the VBA and not just using a formula in the worksheet cell of =B1 )

Here is one of the many examples I have tried in my function just to get the VLOOKUP to work.

Function GetQty(Celly As Range, CellyRange As Range, Colretval As Integer) As Integer
Dim result As String
Dim sheet As Worksheet
Set sheet = ActiveWorkbook.Sheets("Sheet1")
GetQty = Application.WorksheetFunction.VLookup(sheet.Range(CellyRange), sheet.Range(CellyTable), Colretval, False)
End Function

I have tried many examples but I always get #NAME? error

I don't see any advantage using a UDF over the native VLOOKUP function here ! It will just be much slower. Do you have any reason for this ? — Patrick Honorez
– Patrick Honorez, Commented Dec 14, 2011 at 16:48

Gaijinhunter · Accepted Answer · 2011-12-14 13:15:42Z

2

There are a few issues, mainly:

First argument for Vlookup cannot be a range, needs to be a value
It's safer to not declare the return type as Integer

Here's a working example of how to use Vlookup from VBA, maybe it'll help (you call it just VLookup):

Function VBAVlookup(ByVal search As Variant, _
                    cell_range As Range, _
                    offset As Long, _
                    Optional opt As Boolean = False)

Dim result As Variant
result = WorksheetFunction.VLookup(search, cell_range, offset, opt)
'do some cool things to result

VBAVlookup = result

End Function

Unless what you are doing is rather sophisticated, you can always use =Vlookup(...) * 55 +2 and stuff like that to return a manipulated result from vlookup using just one formula.

answered Dec 14, 2011 at 13:15

Gaijinhunter

14.7k4 gold badges53 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sniff The Glove Over a year ago

I have tried the above function, change the call to the function from the worksheet to include a value instead of a cell reference but I still getting #NAME? error. I have tried =VBAVlookup("French",$D$1:$E$4,2) and also =VBAVlookup("French",D1:E4,2) but no luck, how would I call the function from a cell

Gaijinhunter Over a year ago

You're doing it right. The issue must be your macro security as it's not registering that there is a function of that name. Make sure that you are using a macro-enabled book and that your settings are medium or below.

Tim Williams Over a year ago

@user: Where are you putting your code? Should be in a regular module, not in the sheet module.

Sniff The Glove Over a year ago

Code is in a module (not worksheet code), also my Macro settings is Enable for all macro's

Sniff The Glove Over a year ago

I have no idea what happened but after a few month hours of fiddling around the function just started working, how I do not know. Anyway thanks as the above code and the way I was calling it above all works. I have just one query, in the function when you use "result = WorksheetFunction.VLookup(search, cell_range, offset, opt)" is there any way to specify the worksheet to the variable cell_range as I hope to iterate through all works sheets using For Each ws In Worksheets eg something like adding ws.cell_range

Andrey Korneyev · Accepted Answer · 2014-12-16 07:40:25Z

0

Below is the sample for getting the range of a column:

Range("A2", Range("A2").End(xlDown)).Rows.Count

Customize you way, so this may help you passing your range.

edited Dec 16, 2014 at 7:40

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

answered Dec 16, 2014 at 7:10

Shankar

1

Collectives™ on Stack Overflow

Using VLOOKUP in an Excel 2007 VBA Function

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related