Returning the row numbers of all instances of a value which occur within a range?

I've been trying to figure this out all morning. I'll try to simplify the question.

I have a Macro that is finding specific values for each date from one Workbook (we'll call it Workbook A), and copying them to another (Workbook B).

My issue, is that Workbook A is formatted into 3 different tables, one stacked on top of the other. Each of the 3 tables expands throughout the month by adding new rows for the each new day - this means I can't make my formulas pull values from a fixed row. I need to find the row numbers of each of the 3 instances when my date occurs in column A.

This is a simplified example of what a worksheet on Workbook A would like on January 3rd.

	A	B
1	Date	Some Data
2	1/1/15	$1234
3	1/2/15	$1341
4	1/3/15	$1343

5	Date	Other Data
6	1/1/15	$2342
7	1/2/15	$4423
8	1/3/15	$4432

9	Date	Other Data 2
10	1/1/15	blala
11	1/2/15	alkd
12	1/3/15	ddaf

I need my macro to find each instance of 1/3/15 in column A, and return the row number of each instance. I then need to be able to pull out & work with each row number individually in my subsequent formals.
So if I'm trying to find 1/3/15, I want my formula to return (4, 8, 12).

Then I need to be able to work with each row in separate formulas so that I can specify.
(4,B)
(8,B)
(12,B)
Within my macro.

I figure its some sort of Array function, but I have no experience working with these.
Any ideas for this?

Thanks so much!

I commented in the code where'd you want to put your code for working with each found row. BTW, instead of loading an array and then cycling through that array, you could just go ahead and run some code every time your date is found. I wrote the code the way you asked, but can do it my suggested way, too.

Please Login or Register  to view this content.

In workbook B, enter the date into cell B3, and use the array formula (Enter using Ctrl-Shift-Enter instead of just Enter) - this assumes that your sheet in workbook A is named Data....

=IFERROR(LARGE(('[Workbook A.xlsm]Data'!$A$1:$A$1000=$B$3)*ROW($A$1:$A$1000),COUNTIF('[Workbook A.xlsm]Data'!$A:$A,$B$3)-(ROWS($A$1:A1)-1)),"")

Then copy down until the formula returns blanks.

If you want an VBA function / code that can do the same as a worksheet function, then post back.