# OnError Exit Sub Help ??

#### markh1182

##### New Member
Hi, I have the following code:
Code:
``````Sub SummarySheet2()

FinalRow = Sheets("Summary").Range("E50")
a = 2
x = Sheets.Count

For i = 1 To x

If Sheets("Detail " & i).Name = ("Detail " & i) Then

a = a + 1
Sheets("Summary").Select
Range("E" & a).Value = "Detail " & i & " Summary"
Range("H" & a).Formula = "=sum('Detail " & i & "'!M:M)/2"
Range("I" & a).Formula = "=sum('Detail " & i & "'!N:N)/2"
Range("J" & a).Formula = "=sum('Detail " & i & "'!O:O)/2"

End If
Next i
Exit Sub

End Sub``````

If I leave the exit sub after next i, then this does what I want to start with but then fails. If I move the Exit Sub above End If then it does not error on me, but doesn't run properly. Any help would be much appreciated.
Thanks, Mark

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### fabriciofcmiranda

##### Board Regular
I don't know if this is what you mean:

Code:
``````Sub SummarySheet2()

FinalRow = Sheets("Summary").Range("E50")
a = 2
x = Sheets.Count

For i = 1 To x

If Sheets("Detail " & i).Name = ("Detail " & i) Then

a = a + 1
Sheets("Summary").Select
Range("E" & a).Value = "Detail " & i & " Summary"
Range("H" & a).Formula = "=sum('Detail " & i & "'!M:M)/2"
Range("I" & a).Formula = "=sum('Detail " & i & "'!N:N)/2"
Range("J" & a).Formula = "=sum('Detail " & i & "'!O:O)/2"
Else
Exit Sub
End If

Next i

End Sub``````

#### markh1182

##### New Member
still fails on the If statement. What it is I'm trying to do is sheet names are like Detail 1, Detail 2 etc. But sometime you could just have Detail 1 and sometimes it could be up Detail 5. I need something to capture all instances.

try this.
Exit For

#### Scott Huish

##### MrExcel MVP
Does this work for you?

Code:
``````Sub SummarySheet2()
Dim ws As Worksheet, sn As String
For Each ws In Worksheets
If Left(ws.Name, 7) = "Detail " Then
sn = ws.Name
a = Val(Mid(sn, 8))
With Sheets("Summary")
.Range("E" & a) = "Detail " & a & " Summary"
.Range("H" & a).Formula = "=sum('Detail " & a & "'!M:M)/2"
.Range("I" & a).Formula = "=sum('Detail " & a & "'!N:N)/2"
.Range("J" & a).Formula = "=sum('Detail " & a & "'!O:O)/2"
End With
End If
Next
End Sub``````

#### markh1182

##### New Member
Had to make one small amendment to your code Hotpepper but yep that seems to work.
Much Appreciated, Mark

Replies
4
Views
31
Replies
8
Views
130
Replies
8
Views
229
Replies
3
Views
52
Replies
4
Views
54