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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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

J
  • Question
Repeat VBA for each column
Replies
3
Views
223
Alex Blakenburg
A
U
  • Solved
Newbie Seeking Help with Ranges, Loops, and Select
2 3
Replies
27
Views
348
USNA91
U
U
  • Question
Seeking Help with CELLS Property in VBA
2
Replies
13
Views
241
Alex Blakenburg
A
rjplante
  • Solved
Change from Set as Default to Set as Active printer
Replies
1
Views
217
NdNoviceHlp
NdNoviceHlp
N
  • Solved
How to open one workbook find a max value in one worksheet and return it from a function
Replies
2
Views
170
Nicole H
N

Forum statistics

Threads
1,214,923
Messages
6,122,286
Members
449,076
Latest member
kenyanscott

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