loop into array

[abenitez77]

How do I put this all into an array ? I later have to use these variables to insert into a sql table.

             strBanner = ""
             strBannerCode = ""
             strBanner2 = ""
             strBannerCode2 = ""
             strBanner3 = ""
             strBannerCode3 = ""
             strBanner4 = ""
             strBannerCode4 = ""
             strBanner5 = ""
             strBannerCode5 = ""
             strBanner6 = ""
             strBannerCode6 = ""
             strBanner7 = ""
             strBannerCode7 = ""
             strBanner8 = ""
             strBannerCode8 = ""
             strBanner9 = ""
             strBannerCode9 = ""
             strBanner10 = ""
             strBannerCode10 = ""
             
             'Search for "X" markers **************************************************************************************
             mySearch5 = "X"
             Set iSheetE = Range("A44:O54").Find(What:=mySearch5, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
             If Not iSheetE Is Nothing Then
                 strBanner = Trim(iSheetE.Offset(0, 1).Text)
                 strBannerCode = Trim(iSheetE.Offset(0, 2).Text)
                 MyAddr = iSheetE.Address
             Else
                 GoTo NoMoreX
             End If
             
             'Search Next X
             Set iSheetE2 = .FindNext(iSheetE)
             If Not iSheetE2 Is Nothing And MyAddr <> iSheetE2.Address Then
                 strBanner2 = Trim(iSheetE2.Offset(0, 1).Text)
                 strBannerCode2 = Trim(iSheetE2.Offset(0, 2).Text)
             Else
                 GoTo NoMoreX
             End If
             
             'Search Next X
             Set iSheetE3 = .FindNext(iSheetE2)
             If Not iSheetE3 Is Nothing And MyAddr <> iSheetE3.Address Then
                 strBanner3 = Trim(iSheetE3.Offset(0, 1).Text)
                 strBannerCode3 = Trim(iSheetE3.Offset(0, 2).Text)
             Else
                 GoTo NoMoreX
             End If
             
             'Search Next X
             Set iSheetE4 = .FindNext(iSheetE3)
             If Not iSheetE4 Is Nothing And MyAddr <> iSheetE4.Address Then
                 strBanner4 = Trim(iSheetE4.Offset(0, 1).Text)
                 strBannerCode4 = Trim(iSheetE.Offset(0, 2).Text)
             Else
                 GoTo NoMoreX
             End If
             
             'Search Next X
             Set iSheetE5 = .FindNext(iSheetE4)
             If Not iSheetE5 Is Nothing And MyAddr <> iSheetE5.Address Then
                 strBanner5 = Trim(iSheetE5.Offset(0, 1).Text)
                 strBannerCode5 = Trim(iSheetE5.Offset(0, 2).Text)
             Else
                 GoTo NoMoreX
             End If
             
             'Search Next X
             Set iSheetE6 = .FindNext(iSheetE5)
             If Not iSheetE6 Is Nothing And MyAddr <> iSheetE6.Address Then
                 strBanner6 = Trim(iSheetE6.Offset(0, 1).Text)
                 strBannerCode6 = Trim(iSheetE6.Offset(0, 2).Text)
             Else
                 GoTo NoMoreX
             End If
             
             'Search Next X
             Set iSheetE7 = .FindNext(iSheetE6)
             If Not iSheetE7 Is Nothing And MyAddr <> iSheetE7.Address Then
                 strBanner7 = Trim(iSheetE7.Offset(0, 1).Text)
                 strBannerCode7 = Trim(iSheetE7.Offset(0, 2).Text)
             Else
                 GoTo NoMoreX
             End If
             
             'Search Next X
             Set iSheetE8 = .FindNext(iSheetE7)
             If Not iSheetE8 Is Nothing And MyAddr <> iSheetE8.Address Then
                 strBanner8 = Trim(iSheetE8.Offset(0, 1).Text)
                 strBannerCode8 = Trim(iSheetE8.Offset(0, 2).Text)
             Else
                 GoTo NoMoreX
             End If
             
             'Search Next X
             Set iSheetE9 = .FindNext(iSheetE8)
             If Not iSheetE9 Is Nothing And MyAddr <> iSheetE9.Address Then
                 strBanner9 = Trim(iSheetE9.Offset(0, 1).Text)
                 strBannerCode9 = Trim(iSheetE9.Offset(0, 2).Text)
             Else
                 GoTo NoMoreX
             End If
             
             'Search Next X
             Set iSheetE10 = .FindNext(iSheetE9)
             If Not iSheetE10 Is Nothing And MyAddr <> iSheetE10.Address Then
                 strBanner10 = Trim(iSheetE10.Offset(0, 1).Text)
                 strBannerCode10 = Trim(iSheetE10.Offset(0, 2).Text)
             Else
                 GoTo NoMoreX
             End If
NoMoreX:
        
        
        ' --------------------------- Get HDR Values from Cells -------------------------------------------
        
        ' ***************************** Insert Header info into XlsHdr Table ********************************
        tSQL = "Insert Into XLSHdr (DealNum, VendDealNum, DateCreated, DealDate, DealType, ActivityType, DeliveryMethod, BillType, CatNum, PCSNum, MCH1, MCH1Desc," & _
                                    "Week, YearNum, DealDuration, PreShipEffDate, DealEffDate, Vendor, VendorNum, SAPVendorNum," & _
                                    "ManAddress, ManCity, ManProvince, ManPostalCode, ManPhone, ManRepName, ManCategory," & _
                                    "BrokerName, BrokerAddress, BrokerCity, BrokerProvince, BrokerPostalCode, BrokerPhone, BrokerRepName, BrokerCategory, Banner, BannerCode," & _
                                    "Banner2, BannerCode2, Banner3, BannerCode3, Banner4, BannerCode4, Banner5, BannerCode5, Banner6, BannerCode6," & _
                                    "Banner7, BannerCode7, Banner8, BannerCode8, Banner9, BannerCode9, Banner10, BannerCode10, TabName, FileHyperLink, FileName, DocumentType) "
                                    
        tSQL = tSQL & "Values('" & strLCLDeal & "','" & strVendorsDeal & "','" & CStr(strDateCreated) & "','" & strDealDate & "','" & strDealType & "','" & strActivityType & "','" & _
                            strDeliveryMethod & "','" & strBillType & "','" & strCatNum & "','" & strPCSNum & "','" & strMCH1 & "','" & strMch1Desc & "','" & strWeek & "','" & _
                            strYear & "','" & strDealDur & "','" & strPreShipEffDate & "','" & strDealEffDate & "','" & _
                            strManVendorName & "','" & strManVendorNum & "','" & strManSAPVendNum & "','" & strManAddress & "','" & strManCity & "','" & strManProvince & "','" & strManPostal & _
                            "','" & strManPhone & "','" & strManRepName & "','" & strManCategory & "','" & strBrokerName & "','" & strBrokerStreet & "','" & strBrokerCity & "','" & _
                            strBrokerProvince & "','" & strBrokerPostal & "','" & strBrokerPhone & "','" & strBrokerRepName & "','" & strBrokerCategory & "','" & strBanner & "','" & _
                            strBannerCode & "','" & strBanner2 & "','" & strBannerCode2 & "','" & strBanner3 & "','" & strBannerCode3 & "','" & strBanner4 & "','" & strBannerCode4 & _
                            "','" & strBanner5 & "','" & strBannerCode5 & "','" & strBanner6 & "','" & strBannerCode6 & "','" & strBanner7 & "','" & strBannerCode7 & "','" & _
                            strBanner8 & "','" & strBannerCode8 & "','" & strBanner9 & "','" & strBannerCode9 & "','" & strBanner10 & "','" & strBannerCode10 & _
                            "','" & xSheetName & "','" & xFilePath & "','" & xFileName & "','" & strXLSType & "')"
                            
        Set rs = Connection2.Execute(tSQL)

 

[dmt32]

Hi,
Try declaring two arrays with required number of elements & use these in your code

Example:

Dim strBanner(1 To 10) As String, strBannerCode(1 To 10) As String

To Intialize each element, existing variable names in your code will need amending by placing brackets around the number part of the name as follows:

'Search for "X" markers **************************************************************************************
             mySearch5 = "X"
             Set iSheetE = Range("A44:O54").Find(What:=mySearch5, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

             If Not iSheetE Is Nothing Then
                 strBanner(1) = Trim(iSheetE.Offset(0, 1).Text)
                 strBannerCode(1) = Trim(iSheetE.Offset(0, 2).Text)
                 MyAddr = iSheetE.Address
             Else
                 GoTo NoMoreX
             End If
            
             'Search Next X
             Set iSheetE2 = .FindNext(iSheetE)
             If Not iSheetE2 Is Nothing And MyAddr <> iSheetE2.Address Then
                 strBanner(2) = Trim(iSheetE2.Offset(0, 1).Text)
                 strBannerCode(2) = Trim(iSheetE2.Offset(0, 2).Text)
             Else
                 GoTo NoMoreX
             End If
 
Etc Etc...............

And do the same in your SQL string

strBannerCode(1) & "','" & strBanner(2) & "','" & strBannerCode(2)

Hopefully, this may do what you want?

Dave

 

[abenitez77]

Dave, thanks...I think this is a good start but I would like to if possible... put this part into an array this way I don't have to keep repeating this:

 'Search Next X
             Set iSheetE2 = .FindNext(iSheetE)
             If Not iSheetE2 Is Nothing And MyAddr <> iSheetE2.Address Then
                 strBanner(2) = Trim(iSheetE2.Offset(0, 1).Text)
                 strBannerCode(2) = Trim(iSheetE2.Offset(0, 2).Text)
             Else
                 GoTo NoMoreX
             End If

 

[Norie]

Do you actually want a loop for that part?

 

[abenitez77]

It would shrink my code...seems like a good idea...but wasn't sure if it works with the ranges and the way I have it setup. This is why I was asking if it can be done.