Solved

Record not found items into array on Excel

Posted on 2011-02-28
3
218 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

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…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

785 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