Solved

Inserting 2 On Error GoTo commands it only works for one......Excel VB

Posted on 2007-11-19
7
183 Views
Last Modified: 2010-04-30
I am trying to tell my code that if it experiences and error in a certain section thatn to goto the next spot. HOwever, when i do this it works for the first error, but then on the second error it does not work and i get the error "object variable or with block variable not set." Here is my code, and thanks for the help

On Error GoTo 10:
    Sheets("TheVlad").Select
    Range("M4:T4").Select
    Selection.find(What:="Grand Total", After:=ActiveCell, SearchDirection:=xlPrevious).Activate
    ActiveSheet.Cells(65536, ActiveCell.Column).End(xlUp).Copy
    Sheets("Reclaimed").Select
    ActiveCell.PasteSpecial (xlPasteValues)
10:
    ActiveCell.Offset(0, 1).Select
   
    On Error GoTo 30:
   
    Sheets("TheVlad").Select
    Range("M4:T4").Select
    Selection.find(What:="[10]", After:=ActiveCell, SearchDirection:=xlPrevious).Activate
    ActiveSheet.Cells(65536, ActiveCell.Column).End(xlUp).Copy
    Sheets("Reclaimed").Select
    ActiveCell.PasteSpecial (xlPasteValues)
30:
    ActiveCell.Offset(0, 1).Select
   
    On Error GoTo 60:
   
    Sheets("TheVlad").Select
    Range("M4:T4").Select
    Selection.find(What:="[30]", After:=ActiveCell, SearchDirection:=xlPrevious).Activate
    ActiveSheet.Cells(65536, ActiveCell.Column).End(xlUp).Copy
    Sheets("Reclaimed").Select
    ActiveCell.PasteSpecial (xlPasteValues)
60:
    ActiveCell.Offset(0, 1).Select
   
    On Error GoTo 120:
   
    Sheets("TheVlad").Select
    Range("M4:T4").Select
    Selection.find(What:="[60]", After:=ActiveCell, SearchDirection:=xlPrevious).Activate
    ActiveSheet.Cells(65536, ActiveCell.Column).End(xlUp).Copy
    Sheets("Reclaimed").Select
    ActiveCell.PasteSpecial (xlPasteValues)
120:
    ActiveCell.Offset(0, 1).Select
0
Comment
Question by:tchristie33
  • 4
  • 2
7 Comments
 
LVL 33

Accepted Solution

by:
roos01 earned 400 total points
ID: 20315066
I normally try to avoid Goto Statement.

Perhaps this piece of code works for you. I based it on the posted code.
Sub Q__22971126()

Dim MyArr As Variant

Dim ws1, ws2 As Worksheet

Set ws1 = Sheets("TheVlad")

Set ws1 = Sheets("Reclaimed")

MyArr = Array("Grand Total", "[10]", "[30]", "[60]")

On Error Resume Next
 

For i = 0 To UBound(MyArr)

    Sheets("TheVlad").Select

    Range("M4:T4").Select

    Selection.Find(What:=MyArr(i), After:=ActiveCell, SearchDirection:=xlPrevious).Activate

    ActiveSheet.Cells(65536, ActiveCell.Column).End(xlUp).Copy

    Sheets("Reclaimed").Select

    ActiveCell.PasteSpecial (xlPasteValues)

Next i
 

On Error GoTo 0
 

End Sub

Open in new window

0
 
LVL 33

Expert Comment

by:roos01
ID: 20315081
I forgot the:"ActiveCell.Offset(0, 1).Select" in the code.
place this before the Next i line
0
 

Author Comment

by:tchristie33
ID: 20315183
why did you have to set ws1 (i don't see that anywhere in the code) also why did you set two different sheets both to ws1?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 33

Expert Comment

by:roos01
ID: 20315196
I started with defining the worksheets, forgot about removing them. it would be better to remove those lines.
0
 

Author Comment

by:tchristie33
ID: 20315340
because you have "On Error Resume Next" that means when it errors on the code Selection.find(What:=MyArr(i), After:=ActiveCell, SearchDirection:=xlPrevious).Activate it then goes to the next line which tells it to copy that cell it is on (that is the wrong cel though) it then pastes in a wrong value instead of no value. (That is why i was using the goto statements so that it wouldn'd paste the wrong value if it couldn't find it in the search.
0
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 100 total points
ID: 20315939
The only time that most VB6 programmers use the GoTo statement is after On Error.

However, I think that routinely using error trapping to control the program flow is not good practice. But then neither is using On Error resume Next.

The reason that you cannot do what you are trying is that when the error is detected, you are still in the error handler. If you get an error at that time, the usual error message will be displayed.

Do a Resume to prevent that happening:

On Error GoTo 10:
    Sheets("TheVlad").Select
    Range("M4:T4").Select
    Selection.Find(What:="Grand Total", After:=ActiveCell, SearchDirection:=xlPrevious).Activate
    ActiveSheet.Cells(65536, ActiveCell.Column).End(xlUp).Copy
    Sheets("Reclaimed").Select
    ActiveCell.PasteSpecial (xlPasteValues)
10:
    If Err > 0 Then
        Resume 11
    End If
11:
    ActiveCell.Offset(0, 1).Select


However, it is far better to avoid getting errors in the first place.
0
 
LVL 33

Expert Comment

by:roos01
ID: 20317744
true on the on erre reume next,
still therefor I used an array, if no mathc is found it continues trying to search for the next one.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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…
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 …
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…

930 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now