Solved

excel  and error 2042 and testing for this value

Posted on 2010-09-05
6
1,042 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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 Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

776 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