task list reminder

[mcfadwmc]

i have a spreadsheet where names are in column A and tasks are in columns B to F with dates that they are expected to be completed on

eg, on sheet!1

A B C D E F
Name task 1 task 2 task 3 task A task B
John 28/3/9 4/4/9 12/4/9 29/4/9 15/5/9
martin 15/4/9 28/4/9 11/5/9 16/5/9 29/5/9


the list is 100's of names long and is currently conditionally formatted to highlight dates between a range which is listed on sheet!2 as start and finish dates in B2 and B3

so i search through all these dates to find the ones highlighted and then carry out the task for the person.



what i want to do is create a macro that will create the list of me on sheet2 starting in cell A5 and working downwards. so on sheet2 it will get the start and finish dates from B2 and B3 and find all dates within that range and return a term which relates to the value in column A and the task in row 1.


so in the example above if my start date in B2 was 28/4/9 and my end date in B3 was 30/4/9 then it would return

Martin task 2
john task A


hope i've explained it well enough. Thanks in advance

 

[ColinKJ]

HI mcfadwmc,

You might want to try this:

Code:

Sub GetDates()
SD = Sheets(2).Cells(2, 2)
ED = Sheets(2).Cells(3, 2)
If IsDate(SD) = False Then GoTo endd
If IsDate(ED) = False Then GoTo endd
r = Sheets(1).Range("A65536").End(xlUp).Row
RR = 4
For a = 2 To r
For b = 2 To 4
DT = Sheets(1).Cells(a, b)
If DT >= ST And DT <= ED Then
RR = RR + 1
Sheets(2).Cells(RR, 1) = Sheets(1).Cells(a, 1)
Sheets(2).Cells(RR, 2) = Sheets(1).Cells(1, b)
End If
Next b
Next a
endd:
End Sub

Code:

You could assign the macro to a Ctrl+Key combination to activate it.

Regards

ColinKJ

 

[mcfadwmc]

thanks for the reply colin

it doesn't seem to give any results though

reading through the code i can just about keep up with what it's doing up until the RR = 4 bit but i can't identify a mechanism for returning the values in column A and row 1 for relevant dates



thanks

 

[mcfadwmc]

sorry colin

when i actually entered start and finish dates it ran perfectly


thanks very much