Solved

Record not found items into array on Excel

Posted on 2011-02-28
3
217 Views
Last Modified: 2012-08-13
I have the code attachedd that loops through rangs of arrays to check URLS.  What I woudl like is, rather than a messasge box for each failed URL check, have the failed URLs accumulated so at the end of the loops, all the fails are presented at once - either in one message box or in a form that the user can print.
Private Sub cmdCheckURL_Click()
'Tests to see if the URL is still active and if not, creates a list for the uer
'to investigate and delete or correct
'First, fill the range arrays with their range names for each report group
Call fillRangeArrays

'Determine which report group the user wants to check and then use that range array
If Me.optActive.Value = True Then       'ACTIVE
    intArrayUBound = UBound(strRangeGroupSummActv)
ElseIf Me.optIndex.Value = True Then    'INDEX
    intArrayUBound = UBound(strRangeGroupSummIdx)
ElseIf Me.optActiveDetail = True Then   'ACTIVE-DETAIL
    intArrayUBound = UBound(strRangeGroupDetailActv)
ElseIf Me.optIndexDetail = True Then    'INDEX-DETAIL
    intArrayUBound = UBound(strRangeGroupDetailIdx)
Else
    MsgBox "You must select a Report Choice option", vbOKOnly
    Exit Sub
End If
     
intGroupCounter = 1
Do Until intGroupCounter > intArrayUBound
    
If Me.optActive.Value = True Then       'ACTIVE
    Call fillPortfolioURLStringArray(strRangeGroupSummActv(intGroupCounter))
ElseIf Me.optIndex.Value = True Then    'INDEX
    Call fillPortfolioURLStringArray(strRangeGroupSummIdx(intGroupCounter))
ElseIf Me.optActiveDetail = True Then   'ACTIVE-DETAIL
    Call fillPortfolioURLStringArray(strRangeGroupDetailActv(intGroupCounter))
ElseIf Me.optIndexDetail = True Then    'INDEX-DETAIL
    Call fillPortfolioURLStringArray(strRangeGroupDetailIdx(intGroupCounter))
End If

intURLArrayUBound = UBound(s_blackRockURL())
ingURLCounter = 1
        Do Until ingURLCounter > intURLArrayUBound
            If DoesURLExist(s_blackRockURL(ingURLCounter)) Then
'                Do Nothing as it is ok
            Else
                MsgBox "URL " & vbCrLf & vbCrLf & _
                        s_blackRockURL(ingURLCounter) & vbCrLf & vbCrLf & " does not exist."
            End If
                'Increment the counter as the array index
                ingURLCounter = ingURLCounter + 1
        Loop
        intGroupCounter = intGroupCounter + 1
Loop
MsgBox "URL check is done"

End Sub

Open in new window

0
Comment
Question by:ssmith94015
3 Comments
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 34999524
Untested

Please try this.

Private Sub cmdCheckURL_Click()
    Dim MyArray() As String, strUrlNotFound As String
    'Tests to see if the URL is still active and if not, creates a list for the uer
    'to investigate and delete or correct
    'First, fill the range arrays with their range names for each report group
    Call fillRangeArrays
    
    'Determine which report group the user wants to check and then use that range array
    If Me.optActive.Value = True Then       'ACTIVE
        intArrayUBound = UBound(strRangeGroupSummActv)
    ElseIf Me.optIndex.Value = True Then    'INDEX
        intArrayUBound = UBound(strRangeGroupSummIdx)
    ElseIf Me.optActiveDetail = True Then   'ACTIVE-DETAIL
        intArrayUBound = UBound(strRangeGroupDetailActv)
    ElseIf Me.optIndexDetail = True Then    'INDEX-DETAIL
        intArrayUBound = UBound(strRangeGroupDetailIdx)
    Else
        MsgBox "You must select a Report Choice option", vbOKOnly
        Exit Sub
    End If
         
    intGroupCounter = 1
    Do Until intGroupCounter > intArrayUBound
    
    If Me.optActive.Value = True Then       'ACTIVE
        Call fillPortfolioURLStringArray(strRangeGroupSummActv(intGroupCounter))
    ElseIf Me.optIndex.Value = True Then    'INDEX
        Call fillPortfolioURLStringArray(strRangeGroupSummIdx(intGroupCounter))
    ElseIf Me.optActiveDetail = True Then   'ACTIVE-DETAIL
        Call fillPortfolioURLStringArray(strRangeGroupDetailActv(intGroupCounter))
    ElseIf Me.optIndexDetail = True Then    'INDEX-DETAIL
        Call fillPortfolioURLStringArray(strRangeGroupDetailIdx(intGroupCounter))
    End If
    
    Count = 1
    
    intURLArrayUBound = UBound(s_blackRockURL())
    ingURLCounter = 1
            Do Until ingURLCounter > intURLArrayUBound
                If DoesURLExist(s_blackRockURL(ingURLCounter)) Then
    '                Do Nothing as it is ok
                Else
                    ReDim Preserve MyArray(Count)
                    MyArray(Count) = "URL " & vbCrLf & vbCrLf & _
                            s_blackRockURL(ingURLCounter) & vbCrLf & vbCrLf & " does not exist."
                    Count = Count + 1
                End If
                    'Increment the counter as the array index
                    ingURLCounter = ingURLCounter + 1
            Loop
            intGroupCounter = intGroupCounter + 1
    Loop
    MsgBox "URL check is done and the next list is the list for URL's not found"
    
    
    
    For i = LBound(MyArray) To UBound(MyArray)
        strUrlNotFound = strUrlNotFound & vbNewLine & MyArray(i)
    Next
    
    MsgBox strUrlNotFound
End Sub

Open in new window


Sid
0
 
LVL 12

Expert Comment

by:sdwalker
ID: 34999935
Not even sure you need the array, but I would just store the message in a string.

Good luck,

sdwalker
Private Sub cmdCheckURL_Click()
'Tests to see if the URL is still active and if not, creates a list for the uer
'to investigate and delete or correct
'First, fill the range arrays with their range names for each report group
Call fillRangeArrays

'Determine which report group the user wants to check and then use that range array
If Me.optActive.Value = True Then       'ACTIVE
    intArrayUBound = UBound(strRangeGroupSummActv)
ElseIf Me.optIndex.Value = True Then    'INDEX
    intArrayUBound = UBound(strRangeGroupSummIdx)
ElseIf Me.optActiveDetail = True Then   'ACTIVE-DETAIL
    intArrayUBound = UBound(strRangeGroupDetailActv)
ElseIf Me.optIndexDetail = True Then    'INDEX-DETAIL
    intArrayUBound = UBound(strRangeGroupDetailIdx)
Else
    MsgBox "You must select a Report Choice option", vbOKOnly
    Exit Sub
End If
     
intGroupCounter = 1
Do Until intGroupCounter > intArrayUBound
    
If Me.optActive.Value = True Then       'ACTIVE
    Call fillPortfolioURLStringArray(strRangeGroupSummActv(intGroupCounter))
ElseIf Me.optIndex.Value = True Then    'INDEX
    Call fillPortfolioURLStringArray(strRangeGroupSummIdx(intGroupCounter))
ElseIf Me.optActiveDetail = True Then   'ACTIVE-DETAIL
    Call fillPortfolioURLStringArray(strRangeGroupDetailActv(intGroupCounter))
ElseIf Me.optIndexDetail = True Then    'INDEX-DETAIL
    Call fillPortfolioURLStringArray(strRangeGroupDetailIdx(intGroupCounter))
End If

intURLArrayUBound = UBound(s_blackRockURL())
ingURLCounter = 1
        Do Until ingURLCounter > intURLArrayUBound
            If DoesURLExist(s_blackRockURL(ingURLCounter)) Then
'                Do Nothing as it is ok
            Else
                strMsg = strMsg & s_blackRockURL(ingURLCounter) & vbCrLf 
            End If
                'Increment the counter as the array index
                ingURLCounter = ingURLCounter + 1
        Loop
        intGroupCounter = intGroupCounter + 1
Loop
MsgBox "Missing URLS" & vbcrlf & vbcrlf & strMsg

End Sub

Open in new window

0
 

Author Closing Comment

by:ssmith94015
ID: 35000408
I need the array as all broken URLs were requested to be presented in one batch so I think only showing the message would not work.   I may come back as the user has now requested that this data be stored someone on the worksheet rather than just a notification.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

895 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

15 Experts available now in Live!

Get 1:1 Help Now