application.worksheetFunction.Vlookup(

W

WoodyHays

Board Regular
Joined
May 14, 2002
Messages
185
I have messed with this off and on all day. If someone can set me straight I would appreciate it. I am trying write a function that uses the Vlookup worksheetfuntion in VBA.

Function TestGivingAmount(AssetID As Integer)

Dim AnnualGiving As Long
Dim GivingDetail2 As Range
Set GivingDetail2 = Worksheets("Giving").Range("I17:Z19")

AnnualGiving = Application.WorksheetFunction.VLookup(AssetID, GivingDetail2, 3, False)
end function

It returns a value of 0, but it shouldn't.

Is there something obviously wrong here?

I want it to look up assetID 12, entered from a cell, in range I17:Z19, and return the value in the 3rd colum.

I have tried everything I can think of, even hard coding the values in the code.

Woody Hays
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
This worked for me

Code: 
Function TestGivingAmount(AssetID As Integer) As Long

Dim AnnualGiving As Long
Dim GivingDetail2 As Range
Set GivingDetail2 = Worksheets("Giving").Range("I17:Z19")

TestGivingAmount = Application.WorksheetFunction.VLookup(AssetID, GivingDetail2, 3, False)

End Function
 
Upvote 0
Hi WoodyHays:

Try ...
Code: 
[J13] = WorksheetFunction.VLookup(Range("AssetID"), Range("GivingDetail2"), 3, False)
as depicted in the following simulation ...
Book3
IJKL
13c51
14
15
16
17ab101p
18cd51q
19ef222r
Sheet1


I have assumed that I17:Z19 has been named GivingDetail2, and cell I13 has been given range name of AssetID
 
Upvote 0
"I have tried everything I can think of..."

One way of getting functioning vba for a worksheet formula is to enter the formula normally in the cell, turn on the macro recorder, select the cell, hit f2 & enter, then turn off the recorder. You don;t get the most elegant code, but at least it works!
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
VBA function(_ to use Vlookup()
Replies
8
Views
603
imfarhan
I
J
  • Solved
MsgBox CountIf
Replies
2
Views
65
julhs
J
R
  • Question Question
Compose Email VBA Help
Replies
1
Views
117
Logit
Logit
J
  • Question Question
Repeat VBA for each column
Replies
3
Views
408
Alex Blakenburg
A
S
  • Question Question
VBA : Reflection Workspace extract all screen/pages
Replies
0
Views
127
Samantha27
S

Forum statistics

Threads
1,224,568
Messages
6,179,600
Members
452,927
Latest member
whitfieldcraig

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top