Define a list of values within VBA code

[PatrickO]

Working with Excel 2010 on Windows XP, limited knowledge regarding VBA (still learning)... Thanks in advance for your help.

Basically what I want to do is define a list in my VBA code which I will then use in a loop to copy and paste columns which headings match my values within my defined list. The results will be pasted to another worksheet.

Here is an example of source data:

<table style="border-collapse: collapse; width: 281pt;" border="0" cellpadding="0" cellspacing="0" width="375"><col style="width: 41pt;" width="55"> <col style="width: 48pt;" span="5" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 41pt;" height="20" width="55">Doug</td> <td class="xl63" style="border-left: medium none; width: 48pt;" width="64">Amy</td> <td class="xl63" style="border-left: medium none; width: 48pt;" width="64">Patrick</td> <td class="xl63" style="border-left: medium none; width: 48pt;" width="64">Kathryn</td> <td class="xl63" style="border-left: medium none; width: 48pt;" width="64">Markus</td> <td class="xl63" style="border-left: medium none; width: 48pt;" width="64">Jen</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; border-top: medium none;" height="20">97</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">51</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">3</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">49</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">95</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">38</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; border-top: medium none;" height="20">16</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">75</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">38</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">29</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">67</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">30</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; border-top: medium none;" height="20">35</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">93</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">30</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">19</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">54</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">87</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; border-top: medium none;" height="20">74</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">11</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">89</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">13</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">59</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">37</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; border-top: medium none;" height="20">27</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">4</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">90</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">61</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">71</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">66</td> </tr> </tbody></table>

I would then define the list as:

<table style="border-collapse: collapse; width: 41pt;" border="0" cellpadding="0" cellspacing="0" width="55"><col style="width: 41pt;" width="55"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 41pt;" height="20" width="55">Doug</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Patrick</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Markus</td> </tr> </tbody></table>
Results:

<table style="border-collapse: collapse; width: 137pt;" border="0" cellpadding="0" cellspacing="0" width="183"><col style="width: 41pt;" width="55"> <col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 41pt;" height="20" width="55">Doug</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Patrick</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Markus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">97</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">3</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">95</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">16</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">38</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">67</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">35</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">30</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">54</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">74</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">89</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">59</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">27</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">90</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">71</td> </tr> </tbody></table>

Thanks again for your help.

 

[Weaver]

I'm guessing your real data is more complex than this, but you could try:

Sub test()
Set src = Sheets("sheet1")
Set dst = Sheets("sheet2")
    For i = 1 To 3
        dst.Columns(i).Value = src.Columns(i * 2 - 1).Value
    Next
End Sub

HTH
PS src is the sheet where the data is, dst is where it's going
I've assumed the table starts in column A

 

[Peter_SSs]

Just answering you question about defining a list of names to work with. Here is one way.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Test()<br>    <SPAN style="color:#00007F">Dim</SPAN> myHeadings<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#007F00">'Set up your list like this</SPAN><br>    myHeadings = Array("Doug", "Patrick", "Markus")<br>    <br>    <SPAN style="color:#00007F">For</SPAN> i = 0 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(myHeadings)<br>    <br>        <SPAN style="color:#007F00">'Do something more useful than this with each heading</SPAN><br>        MsgBox myHeadings(i)<br>        <br>    <SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[PatrickO]

Thanks for the response. You are correct in your assumption that the data is a little more complex than I have outlined. I don't necessarily want to take only the odd rows (which is what I am assuming i*2 -1 is doing).

I want to copy/paste the column in which the columns headings match the criteria which I have defined in my code.

Here is how I am thinking about it in my head:

I define the list within the VBA code as Doug, Patrick, Markus

Then the code selects Doug as the criteria and searches the column headings until it finds Doug. Once it finds Doug, it copies that data to is final destination. Once Doug is complete, it will loop to the next criteria, Patrick. It will do the same process until it reaches the end of the defined list.

I guess the biggest part I am struggling with is actually defining the list within the VBA code.

 

[PatrickO]

Just answering you question about defining a list of names to work with. Here is one way.


Sub Test()
Dim myHeadings
Dim i As Long

'Set up your list like this
myHeadings = Array("Doug", "Patrick", "Markus")

For i = 0 To UBound(myHeadings)

'Do something more useful than this with each heading
MsgBox myHeadings(i)

Next i
End Sub

Thanks! I will give this a try and let you know if it works.

 

[Weaver]

try:

myList = array("Doug", "Patrick", "Markus")

Then looping through using

for each heading in myList
    ...
next heading

If it's a very long list, you could use a range of cells.

 

[PatrickO]

Haha, I guess I didn't even think to use a range of cells to define the list. Could you possibly post an example of how I would do that... assuming that my list is in cells A1:A3?

Thanks.

 

[Peter_SSs]

Haha, I guess I didn't even think to use a range of cells to define the list. Could you possibly post an example of how I would do that... assuming that my list is in cells A1:A3?

Thanks.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Test2()<br>    <SPAN style="color:#00007F">Dim</SPAN> myHeadings<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#007F00">'Set up your list like this</SPAN><br>    myHeadings = Range("A1:A3").Value<br>    <br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(myHeadings)<br>    <br>        <SPAN style="color:#007F00">'Do something more useful than this with each heading</SPAN><br>        MsgBox myHeadings(i, 1)<br>        <br>    <SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[PatrickO]

Thanks for your replies. Your solutions worked great!