• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 508
  • Last Modified:

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

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
ssmith94015
Asked:
ssmith94015
  • 3
  • 2
1 Solution
 
roger_karamCommented:
Did you try the good old "On Error Resume Next" right before the loop?

-RK
0
 
roger_karamCommented:
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
 
ssmith94015Author Commented:
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
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.

 
ssmith94015Author Commented:
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
 
NorieCommented:
What sort of error might the URL 'throw'?

Is there anyway to check the URL before that happens?
0
 
roger_karamCommented:
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now