Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error Resume Next
I am using an VLookup and want to execute a specific action if the value is
not returned. ans4 = Application.WorksheetFunction.VLookup(Range("C2"), Range("B6:D2000"), 3, False) On Error Resume Next If "AN ERROR HAS OCCURRED" Then ans4 = "Value Not Defined" End If ..Offset(1, 15).Value = ans4 How do I do this? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error Resume Next
The easy way is this approach:
dim ans4 as Variant ans4 = Application.VLookup(Range("C2"),Range("B6:D2000"), 3, False) If iserror(ans4) Then ans4 = "Value Not Defined" End If ..Offset(1, 15).Value = ans4 -- Regards, Tom Ogilvy "Dan" wrote in message ... I am using an VLookup and want to execute a specific action if the value is not returned. ans4 = Application.WorksheetFunction.VLookup(Range("C2"), Range("B6:D2000"), 3, False) On Error Resume Next If "AN ERROR HAS OCCURRED" Then ans4 = "Value Not Defined" End If .Offset(1, 15).Value = ans4 How do I do this? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error Resume Next
Not working.
If I leave the equation as below then the errow window appears and the app quites. If I add "On Error Resume Next" after the VLookup command, the app continues but the statement ans4 = "Value Not Defined" is skipped. What am I missing? "Tom Ogilvy" wrote: The easy way is this approach: dim ans4 as Variant ans4 = Application.VLookup(Range("C2"),Range("B6:D2000"), 3, False) If iserror(ans4) Then ans4 = "Value Not Defined" End If ..Offset(1, 15).Value = ans4 -- Regards, Tom Ogilvy "Dan" wrote in message ... I am using an VLookup and want to execute a specific action if the value is not returned. ans4 = Application.WorksheetFunction.VLookup(Range("C2"), Range("B6:D2000"), 3, False) On Error Resume Next If "AN ERROR HAS OCCURRED" Then ans4 = "Value Not Defined" End If .Offset(1, 15).Value = ans4 How do I do this? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error Resume Next
The problem is that the error is VLookup and not ans4. ans4 still retained
the last value that was calculated with VLookup and therefore iserror (ans4) will never be true. To get around this: dim ans4 as Variant Do While Not EOF(FileNum) ans4 = Application.VLookup(Range("C2"),Range("B6:D2000"), 3, False) On Error Resume Next If ans4 = "Failed" Then ans4 = "Value Not Defined" End If ..Offset(1, 15).Value = ans4 ans4 = "Failed" Loop Is there an alternate to this patch/bandaide? Thanks "Dan" wrote: Not working. If I leave the equation as below then the errow window appears and the app quites. If I add "On Error Resume Next" after the VLookup command, the app continues but the statement ans4 = "Value Not Defined" is skipped. What am I missing? "Tom Ogilvy" wrote: The easy way is this approach: dim ans4 as Variant ans4 = Application.VLookup(Range("C2"),Range("B6:D2000"), 3, False) If iserror(ans4) Then ans4 = "Value Not Defined" End If ..Offset(1, 15).Value = ans4 -- Regards, Tom Ogilvy "Dan" wrote in message ... I am using an VLookup and want to execute a specific action if the value is not returned. ans4 = Application.WorksheetFunction.VLookup(Range("C2"), Range("B6:D2000"), 3, False) On Error Resume Next If "AN ERROR HAS OCCURRED" Then ans4 = "Value Not Defined" End If .Offset(1, 15).Value = ans4 How do I do this? Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error Resume Next
Take a look at Tom's suggestion once more.
dim ans4 as Variant ans4 = Application.VLookup(Range("C2"),Range("B6:D2000"), 3, False) If iserror(ans4) Then ans4 = "Value Not Defined" End If ans4 does not retain the previous value in this example if there is no match. ans4 returns an error. Then Tom checks for that error--hr doesn't check for the string "Failed", either. No "on error resume next" required. Dan wrote: The problem is that the error is VLookup and not ans4. ans4 still retained the last value that was calculated with VLookup and therefore iserror (ans4) will never be true. To get around this: dim ans4 as Variant Do While Not EOF(FileNum) ans4 = Application.VLookup(Range("C2"),Range("B6:D2000"), 3, False) On Error Resume Next If ans4 = "Failed" Then ans4 = "Value Not Defined" End If .Offset(1, 15).Value = ans4 ans4 = "Failed" Loop Is there an alternate to this patch/bandaide? Thanks "Dan" wrote: Not working. If I leave the equation as below then the errow window appears and the app quites. If I add "On Error Resume Next" after the VLookup command, the app continues but the statement ans4 = "Value Not Defined" is skipped. What am I missing? "Tom Ogilvy" wrote: The easy way is this approach: dim ans4 as Variant ans4 = Application.VLookup(Range("C2"),Range("B6:D2000"), 3, False) If iserror(ans4) Then ans4 = "Value Not Defined" End If ..Offset(1, 15).Value = ans4 -- Regards, Tom Ogilvy "Dan" wrote in message ... I am using an VLookup and want to execute a specific action if the value is not returned. ans4 = Application.WorksheetFunction.VLookup(Range("C2"), Range("B6:D2000"), 3, False) On Error Resume Next If "AN ERROR HAS OCCURRED" Then ans4 = "Value Not Defined" End If .Offset(1, 15).Value = ans4 How do I do this? Thanks -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error Resume Next
Vlookup (as I have implemented it) doesn't raise an error affected by On
Error Resume Next. The result of Vlookup is returned to ans4 each time. If Vlookup is unsuccessful, ans4 will contain the #N/A error same as in the worksheet. Otherwise it will contain the result. Even if an error were raised (and let me say again, it isn't), putting On Error Resume Next after the error has occurred does nothing. Regards, Tom Ogilvy "Dan" wrote in message ... The problem is that the error is VLookup and not ans4. ans4 still retained the last value that was calculated with VLookup and therefore iserror (ans4) will never be true. To get around this: dim ans4 as Variant Do While Not EOF(FileNum) ans4 = Application.VLookup(Range("C2"),Range("B6:D2000"), 3, False) On Error Resume Next If ans4 = "Failed" Then ans4 = "Value Not Defined" End If .Offset(1, 15).Value = ans4 ans4 = "Failed" Loop Is there an alternate to this patch/bandaide? Thanks "Dan" wrote: Not working. If I leave the equation as below then the errow window appears and the app quites. If I add "On Error Resume Next" after the VLookup command, the app continues but the statement ans4 = "Value Not Defined" is skipped. What am I missing? "Tom Ogilvy" wrote: The easy way is this approach: dim ans4 as Variant ans4 = Application.VLookup(Range("C2"),Range("B6:D2000"), 3, False) If iserror(ans4) Then ans4 = "Value Not Defined" End If ..Offset(1, 15).Value = ans4 -- Regards, Tom Ogilvy "Dan" wrote in message ... I am using an VLookup and want to execute a specific action if the value is not returned. ans4 = Application.WorksheetFunction.VLookup(Range("C2"), Range("B6:D2000"), 3, False) On Error Resume Next If "AN ERROR HAS OCCURRED" Then ans4 = "Value Not Defined" End If .Offset(1, 15).Value = ans4 How do I do this? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
on error resume next | Excel Programming | |||
On Error Resume Next | Excel Programming | |||
On Error Resume Next | Excel Programming | |||
On Error Resume Next | Excel Programming | |||
On Error Resume Next | Excel Programming |