loop into array

abenitez77

Board Regular
Joined
Dec 30, 2004
Messages
149
How do I put this all into an array ? I later have to use these variables to insert into a sql table.

Code:
             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)
 
Last edited:

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Hi,
Try declaring two arrays with required number of elements & use these in your code

Example:

Rich (BB code):
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:

Rich (BB code):
'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

Rich (BB code):
strBannerCode(1) & "','" & strBanner(2) & "','" & strBannerCode(2)

Hopefully, this may do what you want?

Dave
 
Upvote 0
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:

Code:
 '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
 
Last edited:
Upvote 0
Do you actually want a loop for that part?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,993
Messages
6,175,845
Members
452,675
Latest member
duongtruc1610

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top