?
Solved

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

Posted on 2007-11-19
7
Medium Priority
?
201 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

649 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