?
Solved

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

Posted on 2007-11-19
7
Medium Priority
?
200 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
[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
  • 4
  • 2
7 Comments
 
LVL 33

Accepted Solution

by:
Jeroen Rosink earned 1600 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:Jeroen Rosink
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 33

Expert Comment

by:Jeroen Rosink
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 400 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:Jeroen Rosink
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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…
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…

801 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