Solved

If error in loop data, skip and go to next item in loop

Posted on 2011-02-28
6
487 Views
Last Modified: 2012-05-11
I have a loop that processes data in a one-dimension array of URLS.  If, say the 5th URL throws an error, how do I tell the code to skip the rest of the loop and start the loop for the next URL?
Public Sub Active(strFormGroupValue As String)
On Error GoTo ErrorHandler
        ' Once we have the named range names array filled, let's count the number of items in the ACTIVE SUMMARY array.
        ' Each array member corresponds to a named range for a group in the strFormGroupValue category.
        intArrayUBound = UBound(strRangeGroupSummActv)

        ' Then we get the URLs for each named range in the array.
        ' We begin with the first range group; subsequent range groups will be processed likewise.
        intGroupCounter = 1
        
        Do Until intGroupCounter > intArrayUBound
            'Capture the URL Report type based on the constants assigned to Report Type
            'range names in the "Settings" Worksheet. These will be used to name the tabs
            'in our exported master report - one for each portfolio & report type.
            If InStr(1, strRangeGroupSummActv(intGroupCounter), "SECTOR") > 0 Then
                strULRTypeName = "SECTOR"
            ElseIf InStr(1, strRangeGroupSummActv(intGroupCounter), "RATING") > 0 Then
                strULRTypeName = "RATING"
            ElseIf InStr(1, strRangeGroupSummActv(intGroupCounter), "MASTER") > 0 Then
                strULRTypeName = "MASTER"
            ElseIf InStr(1, strRangeGroupSummActv(intGroupCounter), "MISCEL") > 0 Then
                strULRTypeName = "MISCEL"
            End If
            
            'For each report group, save all the URLs in its range that corresond to "Active" type portfolios.
            Call fillPortfolioURLStringArray(strRangeGroupSummActv(intGroupCounter))
           
            'Capture the number of URLs we have. We'll save them into a master remote report spreadsheet.
            intURLArrayUBound = UBound(s_blackRockURL())

            ingURLCounter = 1

            Do Until ingURLCounter > intURLArrayUBound
                If s_blackRockURL(ingURLCounter) <> "" Then
                    ' Get the portfolio name from the 'Code' column
                    With ThisWorkbook.Worksheets("Settings").Range(strRangeGroupSummActv(intGroupCounter))
                        Set rngPortCodeRange = .Find(s_blackRockURL(ingURLCounter), LookIn:=xlValues)
                        If Not rngPortCodeRange Is Nothing Then
                            strPortName = Application.Intersect(rngPortCodeRange.EntireRow, _
                            ThisWorkbook.Worksheets("Settings").Range("PROFILE").EntireColumn).Value
                        End If
                    End With
                    
                    'Open the BRS URL report into the BRS workbook
                    '(the URL's own commands determine that it will open in a separate Workbook)
                    Set wkbBRS = Application.Workbooks.Open(Filename:=s_blackRockURL(ingURLCounter)) 'BROKEN URL WILL ERROR OUT HERE
'BUT IF BROKEN, DO NOT WANT TO RESUME NEXT AS IT WILL GO TO THE FOLLOWING CODE'
'HOW DO I TELL IT TO START THE NEXT URL LOOP????
    
                    ' Copy the BRS URL Workbook into the remote workbook
                    wkbBRS.Worksheets(1).Copy before:=wkbRemote.Worksheets(1)
                    
    '*****************THIS IS WHERE NAME IS CREATED FOR TAB***************************************
                    If strULRTypeName = "MISCEL" Then 'Do not want to add any prefix!
                        ActiveSheet.Name = strPortName
                    Else
                        ActiveSheet.Name = strULRTypeName & "-" & strPortName
                    End If
    '*****************END WHERE NAME IS CREATED FOR TAB***************************************
                    
                    'Close the URL report Workbook
                    wkbBRS.Close SaveChanges:=False
        
        '***********START NEW CODE************************
        If strULRTypeName = "SECTOR" Or strULRTypeName = "RATING" Then
                    Call Scaling(ActiveSheet.Name)
        ElseIf strULRTypeName = "MASTER" Then
                    Call NameMasterRanges(ActiveSheet.Name, "MASTER")
        ElseIf strULRTypeName = "MISCEL" Then
                    Call NameMasterRanges(ActiveSheet.Name, "MISCEL")
        End If
        'Colors worksheet tab
                 Call ColorWKSTab(strULRTypeName)
        '***********END NEW CODE************************
        
                    'Increment the counter as the array index
                    ingURLCounter = ingURLCounter + 1
                Else
                    'Increment the counter as the array index
                    ingURLCounter = ingURLCounter + 1
                End If
            Loop
            intGroupCounter = intGroupCounter + 1
        Loop

        Application.DisplayAlerts = False           ' Turn off the 'are you sure you want to delete?' alerts
        
        '***********NEW CODE************************
                    Call TableOfContents(wkbRemote.Name)
                    Call DeleteNamedRanges(wkbRemote.Name)
        '***********NEW CODE************************
        
        Application.DisplayAlerts = True            ' Turn alerts back on
        
        ' Save and close the remote workbook
        With wkbRemote
            .Save
            .Close SaveChanges:=True
        End With
        
        ' Give our user a success message.
        MsgBox Prompt:=strFormGroupValue & " Report Created in " & strFileExportPathSumm, Buttons:=vbOKOnly, Title:="Report Created"
   
    ' Explicitly tidy up
    Set wbkCurWB = Nothing
    Set wkbRemote = Nothing
    Set wkbBRS = Nothing
    
Exit_ErrorHandler:
    Exit Sub
ErrorHandler:
If Err.Number = 1004 Then
    MsgBox "CHECK TO SEE IF " & strPortName & " is an active open account as its Green Package file cannot be opened.  " & vbCrLf & _
            "Correct the problem and re-run the application.  After this process terminates, close the BOND workbook that was created." & vbCrLf & vbCrLf & _
            "                    WARNING: The workbook created by this process now has incomplete data! " & vbCrLf & vbCrLf & _
           "****************************THIS PROCESS WILL NOW TERMINATE**********************************************" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Internal Description: " & Err.Description, vbExclamation + vbOKOnly, "FILE CANNOT BE FOUND IN GREEN PACKAGE!"
            ThisWorkbook.Worksheets("Settings").Range("BA1").Offset(intURLMissCount, 0) = strPortName
            ThisWorkbook.Worksheets("Settings").Range("BA2").Offset(intURLMissCount, 1) = s_blackRockURL(ingURLCounter)
            intURLMissCount = intURLMissCount + 1
            Resume Next  'WILL NOT WORK AS IT NEEDS TO START THE NEXT URL DOWNLOAD
Else
    MsgBox Err.Number & Err.Description
        ' Explicitly tidy up
        Set wbkCurWB = Nothing
        Set wkbRemote = Nothing
        Set wkbBRS = Nothing
        Call SlowDown
            Resume Exit_ErrorHandler
End If
    
End Sub

Open in new window

0
Comment
Question by:ssmith94015
  • 3
  • 2
6 Comments
 
LVL 5

Expert Comment

by:roger_karam
ID: 35000637
Did you try the good old "On Error Resume Next" right before the loop?

-RK
0
 
LVL 5

Accepted Solution

by:
roger_karam earned 500 total points
ID: 35000652
Actually because resume next will keep going with the next line of code you might be better off using:
On Error Goto nextitem

and put in "nextitem:" right before the end of the loop.
0
 

Author Comment

by:ssmith94015
ID: 35000738
roger, let me try that.  Right now, I have a go to Resume LableName and the label just before the start of the loop, but I don't really want any "GOTO" labels.  Let me try the NextItem syntax.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Closing Comment

by:ssmith94015
ID: 35000764
I see this is a label, just in a different place. Looks like a label is the way to go, but your suggestion is a better location,
0
 
LVL 33

Expert Comment

by:Norie
ID: 35000790
What sort of error might the URL 'throw'?

Is there anyway to check the URL before that happens?
0
 
LVL 5

Expert Comment

by:roger_karam
ID: 35001537
Thanks for the points. Regarding the location, you might also want to keep the On Error Goto Errorhandler for after the loop for capturing other errors and getting the error code.

good luck.

-RK
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
This collection of functions covers all the normal rounding methods of just about any numeric value.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now