Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.VLookup Problem...
I am trying to write code that does the equivelant of a worksheet function
like: =IF(ISERROR(VLOOKUP(A2,DATERANGE,1,FALSE)),TRUE,FA LSE) What I am doing is looking at a date on the current worksheet (named IMPORT) in cell A2 and checking to see if that data already exists in my repository by doing a VLOOKUP to a sheet in the same workbook called "RAW_DATA" in the named range "DATERANGE". If the data is already there, I want to throw a message box allowing the user to stop macro execution. Also, this macro may be called from another maco, so is there a way to halt ALL macros? If the data is not there, I want to continue through my process uninterrupted. So far, my macro does everything I want... except for the validation part. For that, I have: 'Perform the validation Dim Res As Variant Res = Application.VLookup(A1, Range("RAW_DATA!DATERANGE"), 1, False) If IsError(Res) = True Then MsgBox ("Data Already Exists!") 'Need an escape to abort all macros here... End If I appreciate the assistance! Regards, Ray |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.VLookup Problem...
Untested:
Dim Res As Variant Res = WorksheetFunction.VLookup(Range("A1"), Range("RAW_DATA! DATERANGE"), 1, False) If IsError(Res) = True Then MsgBox ("Data Already Exists!") End End If RayportingMonkey wrote: I am trying to write code that does the equivelant of a worksheet function like: =IF(ISERROR(VLOOKUP(A2,DATERANGE,1,FALSE)),TRUE,FA LSE) What I am doing is looking at a date on the current worksheet (named IMPORT) in cell A2 and checking to see if that data already exists in my repository by doing a VLOOKUP to a sheet in the same workbook called "RAW_DATA" in the named range "DATERANGE". If the data is already there, I want to throw a message box allowing the user to stop macro execution. Also, this macro may be called from another maco, so is there a way to halt ALL macros? If the data is not there, I want to continue through my process uninterrupted. So far, my macro does everything I want... except for the validation part. For that, I have: 'Perform the validation Dim Res As Variant Res = Application.VLookup(A1, Range("RAW_DATA!DATERANGE"), 1, False) If IsError(Res) = True Then MsgBox ("Data Already Exists!") 'Need an escape to abort all macros here... End If I appreciate the assistance! Regards, Ray |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.VLookup Problem...
Hey JW:
That didn't fix it... However, I did solve it by following your lead and playing with the RANGE variables. Here's what finally worked: 'Perform the validation Dim Res As Variant Res = Application.VLookup(Range("IMPORT!A1"), Range("RAW_DATA!DATERANGE"), 1, False) If IsError(Res) = False Then MsgBox ("Data Already Exists!") End End If Thanks for your assistance! Later- Ray "JW" wrote: Untested: Dim Res As Variant Res = WorksheetFunction.VLookup(Range("A1"), Range("RAW_DATA! DATERANGE"), 1, False) If IsError(Res) = True Then MsgBox ("Data Already Exists!") End End If RayportingMonkey wrote: I am trying to write code that does the equivelant of a worksheet function like: =IF(ISERROR(VLOOKUP(A2,DATERANGE,1,FALSE)),TRUE,FA LSE) What I am doing is looking at a date on the current worksheet (named IMPORT) in cell A2 and checking to see if that data already exists in my repository by doing a VLOOKUP to a sheet in the same workbook called "RAW_DATA" in the named range "DATERANGE". If the data is already there, I want to throw a message box allowing the user to stop macro execution. Also, this macro may be called from another maco, so is there a way to halt ALL macros? If the data is not there, I want to continue through my process uninterrupted. So far, my macro does everything I want... except for the validation part. For that, I have: 'Perform the validation Dim Res As Variant Res = Application.VLookup(A1, Range("RAW_DATA!DATERANGE"), 1, False) If IsError(Res) = True Then MsgBox ("Data Already Exists!") 'Need an escape to abort all macros here... End If I appreciate the assistance! Regards, Ray |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
application.vlookup syntax | Excel Programming | |||
Application.Calculate & VLOOKUP | Excel Programming | |||
Application.Calculate & VLOOKUP | Excel Programming | |||
Application.vlookup problem | Excel Programming | |||
application.worksheetfunction.vlookup | Excel Programming |