Solved

excel  and error 2042 and testing for this value

Posted on 2010-09-05
6
1,081 Views
Last Modified: 2012-05-10
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
Comment
Question by:sydneyguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 167 total points
ID: 33608422
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
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 166 total points
ID: 33608451
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33608457
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Expert Comment

by:Dave Brett
ID: 33608490
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
 
LVL 50

Assisted Solution

by:Dave Brett
Dave Brett earned 167 total points
ID: 33608515
> 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
 

Author Closing Comment

by:sydneyguy
ID: 33739847
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question