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

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

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