Select non-sequential columns

[logius]

Here's the setup: I have a listbox of values (Dates, to be specific) and I want to create a macro that will select corresponding columns of data (the first item in every column will be a date). The thing is that the selected columns may be non-sequential (there may be gaps between selected columns).

For example:
In the given data: (always be in the same format)
5/1/01 6/1/01 7/1/01 8/1/01 9/1/01 10/1/01
2 4 1 8 5 5
5 5 4 3 9 11
3 6 2 7 7 9
7 1 1 2 5 0
There would be a corresponding listbox containing the dates: (dates in listbox can change)
5/1/01
7/1/01
8/1/01
10/1/01
I would only want the columns with these dates to be selected. Should I use HLOOKUP to make my selection? If so, how??

 

[ide]

two ways:
1) add to your listbox the four data column (perhaps set widths to 0 cm), and create the change sub, like:
Private Sub ListBox1_Change()
dim vTwo, vThree, vFour, vFive
vTwo = Me.ListBox1.Column(1, Me.ComboBox1.ListIndex)
vThree = Me.ListBox1.Column(2, Me.ComboBox1.ListIndex)
'(or activesheet.cells(i, j) = Me.ListBox1.Column(2, Me.ComboBox1.ListIndex)
...
msgbox vTwo & ", " & vThree & ...
end sub

2) One-column listbox. In the ListBox1_Change sub add a command to set a worksheet cell value to the ListBox1.value. You could use this value with HLOOKUP function.

 

[logius]

Sorry, I neglected to mention that no action should be taken until the user hits the command button that will be on the Userform with the listbox. I had considered using the _Change event, but the requirements of the macro don't leave that as an option. Thanks for your help, though.