Solved

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

Posted on 2007-11-19
7
180 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

758 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

24 Experts available now in Live!

Get 1:1 Help Now