?
Solved

Record not found items into array on Excel

Posted on 2011-02-28
3
Medium Priority
?
222 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

752 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