Solved

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

Posted on 2011-02-28
6
490 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

776 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