Solved

Record not found items into array on Excel

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

729 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