Solved

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

Posted on 2007-11-19
7
191 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 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: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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

738 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