Error handling Question.

davidi

New Member
Joined
Dec 18, 2008
Messages
12
I am trying to use a find statement that looks in column A for a certain number. If it does find the number, then the program then executes and continues on. If it does not find that number then I want to use another find statement to find another number. This goes on for a few numbers. Code is shown below.
If the program does not find the condition, normally I would get an error message, so to override that I put the Onerror goto ... It seems to me that in the one subroutine you cannot have more than one Onerror goto statement. Is that correct?

Is there a better way to handle error overiding so that the progam does not stop.

This is part of the program:

On Error GoTo 35
Cells.Find(What:="575", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, _
SearchFormat:=False).Activate
Call SubHSelect
Call HPaste
35 On Error GoTo 36
Cells.Find(What:="576", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, _
SearchFormat:=False).Activate
Call SubHSelect
Call HPaste
36 On Error GoTo 40
Cells.Find(What:="577", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, _
SearchFormat:=False).Activate
Call SubHSelect
Call HPaste
GoTo 40



40 end sub
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
You could try it this way@
Code:
    Dim rng As Range
    Set rng = Cells.Find(What:="575", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, _
        SearchFormat:=False)
    If rng Is Nothing Then
        'nothing found, try other finds
    Else
        'something found, cell ref is rng
    End If
 
Upvote 0
Perhaps i best be more specific. This program is updating a number of sheets within a workbook. The first time the update runs through the first sheet the errors are handled Ok - that is the program jumps to where the goto statement tells it to. Its when the program looks at the next sheet and comes across the error to stops the program with the message:
Run Time error 91
Object Variable or with block variable not set.

Now I would have thought that this would have occurred the first time through the program loop. So why is it on the second. (have not been able to do more that one loop through the program as yet

Thanks
 
Upvote 0
Holy cow, it looks like you were attempting to build 40 or more GoTo error traps.

Without knowing the full context of your macro other than the piece of code you posted from it, this would be a better approach, without any GoTo statements. Modify for the start and end number.

Code:
Dim strFind$, varFind As Variant
strFind = "575"
Do
Set varFind = Columns(1).Find(What:=strFind, LookIn:=xlValues, LookAt:=xlPart)
If Not varFind Is Nothing Then
Call SubHSelect
Call HPaste
Else
strFind = Val(strFind) + 1
End If
Loop While Val(strFind) <= 577
 
Last edited:
Upvote 0
Yes Tom,

I was building a number of error traps! Will try your suggestion and see how it goes
 
Upvote 0
Just a final question (hopefully).
The code you wrote works for finding 575 to 577.
Further down my program this changes to a range of 614 to 616. Am I correct in assuming just before this code I just rest the strFind = to the new start number?
 
Upvote 0
Yes, so the code in theory would be of this structure:

Code:
Dim strFind$, varFind As Variant

strFind = "575"
Do
Set varFind = Columns(1).Find(What:=strFind, LookIn:=xlValues, LookAt:=xlPart)
If Not varFind Is Nothing Then
Call SubHSelect
Call HPaste
Else
strFind = Val(strFind) + 1
End If
Loop While Val(strFind) <= 577
 
strFind = "614"
Do
Set varFind = Columns(1).Find(What:=strFind, LookIn:=xlValues, LookAt:=xlPart)
If Not varFind Is Nothing Then
Call SubHSelect
Call HPaste
Else
strFind = Val(strFind) + 1
End If
Loop While Val(strFind) <= 616
 
Upvote 0
Hi Tom,

I tried to run the code and the problem is that the code you provided just below the Do statement needs to "activate" the cell when it finds the strfind value. I thought just adding ".activate" to that line would work but when I tried this, the program activated the cell but then immediately displayed a code 13 error message.

In plain english I want to find the cell that has the code 575 or else 576 etc and then add a row immediately above it and paste a heading selected from a look up table. (thats what the two Call lines do after your own code.)

So how can I activate the cell once it is found by your code:

Set varFind = Columns(1).Find(What:=strFind, LookIn:=xlValues, LookAt:=xlPart)

Thanks
 
Upvote 0
Do both of us a favor and post the code for those two Call macros. Having no idea what they do, we might be back and forth on this all week long, and if there's more to the code you posted, please include that too, in order to give the full picture on what's going on.
 
Upvote 0
Hi Tom,

Here it all is.
Sub HPaste()
ActiveCell.Offset(-1, 0).Select
ActiveSheet.Paste

End Sub
Sub HSelect()
ActiveCell.Offset(1, 0).Select
ActiveCell.Copy
Sheets("Headings").Select
Range("j1").Select
ActiveSheet.Paste
Columns("i:i").Select
Selection.Find(What:=Range("j1").Value, After:=ActiveCell, LookIn _
:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 2).Select
ActiveCell.Copy
End Sub
Sub SubHSelect()
Call InsertR
ActiveCell.Offset(1, 0).Select
ActiveCell.Copy
Sheets("Headings").Select
Range("b1").Select
ActiveSheet.Paste
Columns("a:a").Select
Selection.Find(What:=Range("b1").Value, After:=ActiveCell, LookIn _
:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 2).Select
ActiveCell.Copy
End Sub
Sub RunPrintProgram()
Dim a As Variant
Dim x As Integer
Call UnhideSheets
Call Pastevalues
a = Array("Summary all Programs - P&L", "Corporate Services Summary", "10-70 Corporate Services", _
"10-71 Promotions", "10-77 AAV Resources", "10-78 Dementia Awareness Week", "Support Services Summary", _
"20-29 Memory Lane Cafes", "20-80 Administration", "20-86 Metro & Gippsland", _
"20-87 Rural", "20-88 Gateways", "20-28 Early Stage")
For x = LBound(a) To UBound(a)
Sheets(a(x)).Select
Range("a9").Select
Do While ActiveCell.Value <> Empty
10 If ActiveCell.Offset(0, 1).Value <> Empty Then GoTo 15
If ActiveCell.Offset(0, 2).Value <> Empty Then GoTo 15
If ActiveCell.Offset(0, 3).Value <> Empty Then GoTo 15
If ActiveCell.Offset(0, 4).Value <> Empty Then GoTo 15
Call Deleterow
GoTo 20
15 ActiveCell.Offset(1, 0).Select
20 Loop
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value <> Empty Then GoTo 10
Range("a9").Select
30 Call InsertR
Call HSelect
Sheets(a(x)).Select
Call HPaste
' insert extra subtotals here
If ActiveCell.Value = " 51 TRAVEL & ACCOMMODATION" Then
ActiveCell.Offset(1, 0).Select
Call SubHSelect
Sheets(a(x)).Select
Call HPaste
If ActiveCell.Value = " 511 NON AAV FLEET EXPENSES" Then GoTo 40
On Error GoTo 31
Cells.Find(What:="511", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, _
SearchFormat:=False).Activate
Call SubHSelect
Sheets(a(x)).Select
Call HPaste
GoTo 40
31 On Error GoTo 32
Cells.Find(What:="512", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, _
SearchFormat:=False).Activate
Call SubHSelect
Sheets(a(x)).Select
Call HPaste
GoTo 40
32 On Error GoTo 40
Cells.Find(What:="513", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, _
SearchFormat:=False).Activate
Call SubHSelect
Sheets(a(x)).Select
Call HPaste
GoTo 40

ElseIf ActiveCell.Value = " 54 OTHER OFFICE EXPENSES" Then
ActiveCell.Offset(1, 0).Select
Call SubHSelect
Sheets(a(x)).Select
Call HPaste
If ActiveCell.Value = " 541 PHOTOCOPIER COSTS" Then GoTo 33
If ActiveCell.Value = " 543 FURNITURE" Then GoTo 34
If ActiveCell.Value = " 545 COMPUTERS" Then GoTo 40
On Error GoTo 33
Cells.Find(What:="541", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, _
SearchFormat:=False).Activate
Call SubHSelect
Sheets(a(x)).Select
Call HPaste
33 On Error GoTo 34
Cells.Find(What:="543", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, _
SearchFormat:=False).Activate
Call SubHSelect
Sheets(a(x)).Select
Call HPaste
34 On Error GoTo 40
Cells.Find(What:="545", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, _
SearchFormat:=False).Activate
Call SubHSelect
Sheets(a(x)).Select
Call HPaste
GoTo 40

ElseIf ActiveCell.Value = " 56 PROGRAM DELIVERY COSTS" Then
On Error GoTo 40
Cells.Find(What:="569", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, _
SearchFormat:=False).Activate
Call SubHSelect
Sheets(a(x)).Select
Call HPaste
GoTo 40

ElseIf ActiveCell.Value = " 57 PROMOTIONAL COSTS" Then
On Error GoTo 35
Cells.Find(What:="575", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, _
SearchFormat:=False).Activate
Call SubHSelect
Sheets(a(x)).Select
Call HPaste
35 On Error GoTo 36
Cells.Find(What:="576", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, _
SearchFormat:=False).Activate
Call SubHSelect
Sheets(a(x)).Select
Call HPaste
36 On Error GoTo 40
Cells.Find(What:="577", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, _
SearchFormat:=False).Activate
Call SubHSelect
Sheets(a(x)).Select
Call HPaste
GoTo 40
End If


40 Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, _
SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = Empty Then ActiveCell.Offset(3, 0).Select
If ActiveCell.Value = "Total Salary Expenses" Then Call TotSal
If ActiveCell.Value = "Total Non Salary Expenses" Then GoTo 50
GoTo 30
50 Next x
' ActiveWorkbook.Close savechanges:=False
End Sub
Sub Deleterow()
Selection.EntireRow.Select
Selection.Delete
End Sub
Sub InsertR()
Selection.EntireRow.Select
Selection.Insert Shift:=x1Up
End Sub
Sub UnhideSheets()
Dim wsSheet As Worksheet
For Each wsSheet In ActiveWorkbook.Worksheets
wsSheet.Visible = xlSheetVisible
Next wsSheet
End Sub
Sub Pastevalues()
Sheets("CAPEX BUDGET").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets(Array("CAPEX BUDGET", "summary program results", "Income Summary", _
"Reconciliation", "Department Summary", "Summary all Programs - P&L", _
"Corporate Programs", "Corporate Services Summary", "10-70 Corporate Services", _
"10-71 Promotions", "10-77 AAV Resources", "10-78 Dementia Awareness Week", _
"10-72 Program Establishment", "Support Programs", "Support Services Summary", _
"20-29 Memory Lane Cafes", "20-80 Administration", "20-86 Metro & Gippsland", _
"20-87 Rural", "20-88 Gateways", "20-28 Early Stage", _
"20-81 Dementia Caring Project", "20-85 Telephone & Infrastructur", "20-XX Blank" _
, "Education Programs")).Select
Sheets("CAPEX BUDGET").Activate
Sheets(Array("Education - Summary", "30-51 Traineeships", _
"30-56 Dementia Essentials", "30-57 Dem. Train. Study Centre", _
"30-90 Administration", "30-91 HACC ", "30-93 Fee for Service", "30-94 AA Quals", _
"30-99 NDSP - Ed'n & Training", "30-53 RAP4", "30-50 AAA Website", _
"30-60 Primary Schools Project", "40-75 Fundraising", _
"40-75 Fundraising Sub-Programs", "Strat. Policy & Proj. Programs", _
"Strat. Policy & Project Summary", "50-24 Nat Resources", "50-35 MyM National", _
"50-74 Dementia Policy & Project", "50-76 Collab. Research Centre", _
"50-79 Consultancies", "50-89 D&MCC's", "50-92 Library", "50-97 AAV Website", _
"50-XX National Project")).Select Replace:=False
Sheets("50-36 MyM Victoria").Select Replace:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
Private Sub Boldhead()
'bold heading with no underline
With Selection.Font
.Name = "Arial"
.Size = 10
.Bold = True
.Underline = xlUnderlineStyleNone
End With
End Sub
Sub TotSal()
ActiveCell.Offset(4, 0).Select
Call InsertR
ActiveCell.Value = "Non Salary Expenses"
Call Boldhead
ActiveCell.Offset(1, 0).Select
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top