Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP not working in VBA
I am trying to perform at lookup in a defined range name=DemoEndDate.
Res comes back as blank. Is this the correct format?? Also, does anyone know if this is the correct date format? YY = Year(Worksheets("RFJ").Range("N8")) MM = Month(Worksheets("RFJ").Range("N8")) SDate = DateSerial(YY, MM, 1) Dim YY As String Dim MM As String Dim SDate As Date Res = Application.WorksheetFunction.VLookup(SDate, Range("DemoOEndDate"), 2, False) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP not working in VBA
If res is coming back as blank ("", right?), then I think your =vlookup() is
working fine. But the first match for that date has a cell that looks empty. ====== You're not sharing all your code--are you masking any problems with an "on error resume next" line? ps. I'd use this version--with two important changes. I'd use application.vlookup() instead of application.worksheetfunction.vlookup() because of the way no matches are handled. And I'd use clng(sdate). Dates can be funny things to find matches with. dim Res as variant .... Res = Application.VLookup(clng(SDate), Range("DemoOEndDate"), 2, False) if iserror(res) then 'not found else msgbox res end if DogLover wrote: I am trying to perform at lookup in a defined range name=DemoEndDate. Res comes back as blank. Is this the correct format?? Also, does anyone know if this is the correct date format? YY = Year(Worksheets("RFJ").Range("N8")) MM = Month(Worksheets("RFJ").Range("N8")) SDate = DateSerial(YY, MM, 1) Dim YY As String Dim MM As String Dim SDate As Date Res = Application.WorksheetFunction.VLookup(SDate, Range("DemoOEndDate"), 2, False) -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP not working in VBA
DogLover wrote:
I am trying to perform at lookup in a defined range name=DemoEndDate. Res comes back as blank. Is this the correct format?? Also, does anyone know if this is the correct date format? YY = Year(Worksheets("RFJ").Range("N8")) MM = Month(Worksheets("RFJ").Range("N8")) SDate = DateSerial(YY, MM, 1) Dim YY As String Dim MM As String Dim SDate As Date Res = Application.WorksheetFunction.VLookup(SDate, Range("DemoOEndDate"), 2, False) You could try this modification: Res = Application.WorksheetFunction.VLookup(CLng(SDate), Range("DemoOEndDate"), 2, False) See also my comments in this recent thread: http://preview.tinyurl.com/y8rlfry |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP not working in VBA
These changes worked! Thank you both!! "DogLover" wrote: I am trying to perform at lookup in a defined range name=DemoEndDate. Res comes back as blank. Is this the correct format?? Also, does anyone know if this is the correct date format? YY = Year(Worksheets("RFJ").Range("N8")) MM = Month(Worksheets("RFJ").Range("N8")) SDate = DateSerial(YY, MM, 1) Dim YY As String Dim MM As String Dim SDate As Date Res = Application.WorksheetFunction.VLookup(SDate, Range("DemoOEndDate"), 2, False) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
vlookup not working | Excel Programming | |||
VLookup not working | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Discussion (Misc queries) | |||
Vlookup not working for me. | Excel Worksheet Functions |