Solved

excel  and error 2042 and testing for this value

Posted on 2010-09-05
6
1,072 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
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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…

749 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