Returning Columns to a List Box for a Holiday LookUp Table

[Noggy1]

Guys - I need help with this little problem!

I have a worksheet that holds everyone's holiday period, that is the
first day of their holiday in one cell and the last day of their
holiday in another. The structure of this table is that row one
holidays the employee name across two merged cells, this means that
each employee has two columns in order to hold the dates of their
holiday!

The problem that I want to crack is through use of a user form, how do
I return all of holiday dates to a list box when I search on a
employee's name? I have created a form with a text box where I can
enter in a name, and I can find the name using the Find function, but I
am stuck as to how to copy the correct columns giving the start and end
of the holiday.

There could anything up to 200 names on the table.

The other thing to note are that the dates (not the names) are linked
to cells in other workbooks as each employee holds their own
holidaysheet, and I think this may complicate the search.

Can anyone help with this?????

 

[K Dales]

The links to other workbooks should not be a problem as long as everything is
up to date and calculated on the sheet you are using at the time you do the
search.

As for how to find the dates: If you have found the employee name (as you
say you can) then (if I visualize this correctly) the start and end dates are
directly beneath the merged cell with that name in it? The find function
will return the address of the upper left cell that makes up the merged cells
- in your case, this means that (address-wise) the start date is directly
under the found cell, the end date is one cell to the right of that:
StartDate = Range("1:1").Find(EmployeeName).Cells(2,1).Value
EndDate = Range("1:1").Find(EmployeeName).Cells(2,2).Value
I am not clear how you want to use these with your list box, but this is how
you can find the dates.