Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-06-2016, 07:42 AM
Mark.Christian Mark.Christian is offline Excel VBA Vlookup function help Windows 7 64bit Excel VBA Vlookup function help Office 2010 32bit
Novice
Excel VBA Vlookup function help
 
Join Date: Jul 2016
Posts: 1
Mark.Christian is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 07-19-2016, 11:32 AM
charlesdh charlesdh is offline Excel VBA Vlookup function help Windows 7 32bit Excel VBA Vlookup function help Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

Hi,

It would help if you attached a copy of you file.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel VBA Vlookup function help VLookup and Nested IFs or Index Function SavGDK Excel 10 04-08-2016 10:06 PM
Excel VBA Vlookup function help VLOOKUP is dependent up IF function dmccrar Excel 2 09-14-2014 09:51 AM
Excel VBA Vlookup function help 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
Excel VBA Vlookup function help Is this possible using the Vlookup or any other function? Delson Excel 4 02-08-2010 01:27 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:26 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft