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) |
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 |
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 |
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) |
All times are GMT +1. The time now is 10:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com