[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

excel  and error 2042 and testing for this value

Posted on 2010-09-05
6
Medium Priority
?
1,177 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 668 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 664 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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 668 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

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

650 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