Solved

Record not found items into array on Excel

Posted on 2011-02-28
3
214 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This collection of functions covers all the normal rounding methods of just about any numeric value.
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 will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

772 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

10 Experts available now in Live!

Get 1:1 Help Now