OnError Exit Sub Help ??

[markh1182]

Hi, I have the following 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

 

[fabriciofcmiranda]

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

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]

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.

 

[danial]

try this.
Exit For

 

[Scott Huish]

Does this work for you?

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]

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