##### Board Regular
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
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

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

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

##### Well-known Member

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

##### Board Regular

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

##### Board Regular
sorry colin

when i actually entered start and finish dates it ran perfectly

thanks very much

Replies
2
Views
470
Replies
5
Views
249
Replies
7
Views
318
Replies
0
Views
455
Replies
8
Views
646

1,190,655
Messages
5,982,131
Members
439,757
Latest member
85Sarah2005

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back