I am having trouble with the following:

I have a row of dates named “Ref_Date”.
I have an input date cell, “Enter_Date”, where the user inputs a date.

I have a sheet of data which contains people’s names in the first
column and then a bunch of entries of data in the corresponding rows.
The data section is named “Data_Entries” and the name column is named
“Name_Entries”.

Here is what I need:

I need code that will first go through the “Ref_Date” section and find
the column that contains the matching date defined in “Enter_Date”.

When it finds a date that matches the “Enter_Date” value it should
begin a search of the column below this matching date within
“Data_Entries”. During this second search it looks for a specific type
of data that will be defined by a variable named “val_lookup” that is
defined earlier in the code.

Just as an example, lets say I define the “val_lookup” variable as a
“B”. Now as it searches the column for an instance of “B”, if it comes
to a “B” value, I want it to then reference the name of the person who
corresponds to this “B” value within the “Name_Entries” range.

Once it has this name I would like it to take this name and place it in
the first available cell in a named range called “B_Instances”.

After that I want it to continue searching for more instances of “B”s
and as they are found I want it to again place the corresponding name
into the next available cell in “B_Instances”.

Below is an example:

ENTER DATE: 6 MAY 06

........REF DATE: 4 MAY 06 ....5 MAY 06 ....6 MAY 06
NAMES:
Bian.....................A.................A...... ............B
Chad....................B.................B....... ...........A
Tom.....................C.................A....... ...........B

Resulting "B_Instances" section would read

Brian
Tom

I figure I could do this with a For, If, For, If statement but can’t
fill in the middle part of the code below which is initiated by
pressing a button:

Private Sub CommandButton1_Click()
val_lookup = "B"


Code:
--------------------
For Each c In Range("Ref_Date")
If c.Value = Worksheets("Sched").Range("Enter_Date") Then

''This is what I can't figure out but figure the logic should be something like ... search the column of this matching date for any instance of "B" defined by "val_lookup" and if one is found reference the corresponding name in the first column of this row within "Name_Entries" and place it into the first cell of the named array "B_Instance". Then continue searching the rest of the column and if another instance of "B" is found reference that person's name and put it in the second cell of "B_Instance" and so on and so forth"

End If
Next c
End Sub
--------------------


--
BrianDP1977
------------------------------------------------------------------------
BrianDP1977's Profile: http://www.excelforum.com/member.php...o&userid=29110
View this thread: http://www.excelforum.com/showthread...hreadid=543127