• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1274
  • Last Modified:

excel and error 2042 and testing for this value

i am playing with some vlookups in excel and some vba, and when no correct data is found or out of range it returns an error 2042

was wondering how i can check for this value and then reset it as required
thanks in advance
error2042.png
0
sydneyguy
Asked:
sydneyguy
3 Solutions
 
cyberkiwiCommented:
You can turn on error checking using

On Error Resume Next
Err.Clear
Application.Goto Reference:="TissueCellsPUVLookUP"
If Err.Number <> 0 Then ' an error occurred
   '' handle the error
End If
On Error Goto 0
0
 
rockiroadsCommented:
A quick lesson in error handling in vba

In VBA there is a object called Err which is used to handle errors. Two properties of note in there is Number and Description.
I normally code the way cyberkiwi as shown as I prefer the nice flow of things.
eg
if err.number <> 0 then
    msgbox "Error " & err.number & " occurred when doing something. " & err.description
else
    'continue code



There are other ways to handle errors also like labels.

3 ways are

On Error Goto 0 - this is the default and what you are experiencing now
On Error Resume Next - this continues running the code, ignoring the error but Err object would be populated
On Error Goto xxx - this is the label where xxx is the label

eg

    On Error Goto xxx:

    'Your code here one line may cause an error
    Exit Sub
xxx:
    'Your code to handle the error
    Resume Next
End Sub


The resume next is used to tell it to continue running from where it left off
0
 
rockiroadsCommented:
Forgot to mention about labels, it is important to remember the code will drop into it, but if you do not want that then ensure you either exit the procedure (normal practice is to put error handler on the end) or another goto another label.

Useful things for label is like cleanup. If you created objects and want them cleaned up after successful processing or failure, but remember to check the error status
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Dave BrettVice President - Business EvaluationCommented:
This isn't a VBA run time error, its a cell evaluation error

You would handle it as below

Cheers

Dave

If Not IsError(ActiveCell) Then
 ' stuff
Else
 ' was error
End If

Open in new window

0
 
Dave BrettVice President - Business EvaluationCommented:
> This isn't a VBA run time error, its a cell evaluation error

In case this needs further explaining, the error comes from the data evaluation, ie looking at a cell that evalutes to an Error condition (say NA() etc)

As it is not a VBA  run time error then
On Error Resume Next
is not the appropriate technique to handle it, you need to evaluate the cell for an error instead as per the code above

if you put a
=NA()
into cell A1 to replicate a failed lookup, then run my code above with A1 selected  you will see that it detects the problem
then retry with say "Lord Howe" in A1

Cheers

Dve

0
 
sydneyguyAuthor Commented:
thanks for your help after spending hours on getting the sheet up and going i found out that 50% of my users willbe mac and the next 10% will be running 2007 and 2010 so have decided to can the whole idea and go to a web based system
so thanks for your help it was very helpful on this project
garry
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now