#1
|
|||
|
|||
Excel VBA Vlookup function help
Hi, I'm trying to create a vlookup function in Excel 2010 (32-bit) to retrieve values from a data table named ForeSee. The table's left most column is a range of dates from 12/1/2015 to 12/31/2017 in ascending order. The value of the first cell is 12/1/2015, so it does not include the header row.
As I intend to use the VbaVlookup() results in another function, I've created a subroutine to test the function. The problem is that I get "Error 2042" (N/A#) as a result when I run the test, even though I debug it, and I can see the range correctly defined. Public Sub Test() Dim strRange As String Dim intOffset As Integer Dim varValue As Variant strRange = "ForeSee" varValue = "07/03/2016" intOffset = 2 Debug.Print VbaVLookup(varValue, strRange, intOffset) End Sub Public Function VbaVLookup(ByVal varValue As Variant, ByVal strRange As String, ByVal intOffset As Integer) As Variant Dim strSheet As String Dim rngTable As Range On Error Resume Next strSheet = Range(strRange).Worksheet.Name Set rngTable = Worksheets(strSheet).Range(strRange) 'checks to see if first cell in table is a date and converts the Lookupvalue to a date value With rngTable If IsDate(Worksheets(strSheet).Cells(.Row, .Column).Value) And IsDate(varValue) Then VbaVLookup = Application.VLookup(CDate(varValue), rngTable, intOffset, False) Else VbaVLookup = Application.VLookup(varValue, rngTable, intOffset, False) End If Debug.Print "Row - " & .Row & " Col - " & .Column & " Rows - " & .Rows.Count & " Cols - " & .Columns.Count End With On Error GoTo 0 Exit_Routine: End Function |
#2
|
|||
|
|||
Hi,
It would help if you attached a copy of you file. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
VLookup and Nested IFs or Index Function | SavGDK | Excel | 10 | 04-08-2016 10:06 PM |
VLOOKUP is dependent up IF function | dmccrar | Excel | 2 | 09-14-2014 09:51 AM |
Using If and Vlookup Function | jassi.mgg@gmail.com | Excel | 5 | 03-16-2014 01:47 AM |
Using vlookup with the IF function | CSzoke | Excel | 11 | 06-01-2013 10:56 PM |
Is this possible using the Vlookup or any other function? | Delson | Excel | 4 | 02-08-2010 01:27 PM |