Looping through two dimensional Array

JohnCDK

New Member
Joined
Jan 25, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Good morning,

So I am new to using arrays. But here is what I am trying to do.
I have a table (listobject) with two columns.
Basically these made up values is what the table looks like, but with something like 100+ rows
Server Library
200.123.123.23 ABC
201.124.243.24 DEF
231.123.123.54 GHI

What I want to do is to put that in an array and loop through each row. So the result would be that a "Server" and "Library" variable in my code becomes the values in the next row in the array with each loop.

So far I have figured out how to populate the array with the table using this.

Liblist = Worksheets("Library").ListObjects("Library").DataBodyRange.Value

And for testing I can cycle through the array values. in a Msgbox.

The below will cycle through the first column of values and then the second column of values.
Which makes me wonder if the array is actually 1 dimensional?

Sub arraytest()
Dim Liblist As Variant
Dim Library As Variant
Dim Server As Variant

Liblist = Worksheets("Library").ListObjects("Library").DataBodyRange.Value

For Each Server In Liblist
MsgBox Server
Next
End Sub

The part where I am lost is how to make sure my array is actually two dimensional and during each loop MsgBox the Server and Library column values for the row that the loop is on.
Or how to know I am actually looping through rows of the array and not just each element.
Or how to reference each column value for the row the loop is on, which calling out exact coordinates

If I can get that far then I will know that I can use the Server and Library in my code and know they are changing with each loop.

Anyhow, I appreciate your time and thanks in advance for helping me understand how to make this work.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,611
Office Version
  1. 365
Platform
  1. Windows
When you load an array direct from the sheet like that, it will always be a 2D array.
Your code is looping through each element, to loop through a "column" you can use
VBA Code:
For i = 1 To UBound(libList)
   MsgBox libList(i, 1) & vbLf & libList(i, 2)
Next i
 
Solution

JohnCDK

New Member
Joined
Jan 25, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
When you load an array direct from the sheet like that, it will always be a 2D array.
Your code is looping through each element, to loop through a "column" you can use
VBA Code:
For i = 1 To UBound(libList)
   MsgBox libList(i, 1) & vbLf & libList(i, 2)
Next i
Thank you very much, I think this is exactly what I have been scouring the web for.

So if I am following correctly these become my server and library variables.
- Server - Liblist(i, 1)
- Library - Liblist(i, 2)

So this connection string.....

- DBConnection.Open "DRIVER={iSeries Access ODBC Driver};SYSTEM=" & Server & ";DBQ=" & Library & ";Uid=XXX;Pwd=XXX;TRANSLATE=1;"

Gets changed to......

- DBConnection.Open "DRIVER={iSeries Access ODBC Driver};SYSTEM=" & Liblist(i, 1) & ";DBQ=" & Liblist(i, 2) & ";Uid=XXX;Pwd=XXX;TRANSLATE=1;"

Correct?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,611
Office Version
  1. 365
Platform
  1. Windows
That looks right, yes. :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,611
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,709
Messages
5,626,411
Members
416,183
Latest member
IanA

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
Top