Sleeper: Create table array for Vlookup with match and count to next blank row

I have a VBA that creates a summary sheet of about 20 tabs of Building data based on criteria. The summary sheet then list the Header (Building) and then the lines of account numbers and data. The same account numbers could be listed under different buildings - hence a traditional vlookup won't work (an account exists on all buildings but may not be on summary sheet). Managers take the summary sheet and insert comments. Now I need to get the comments they made BACK to the original Building tab. I would like to have a VBA that inserts the following vlookup formula into the same column on all Building spreadsheets and copy the comments back. How do I get Y to work below???


This is how I want my vlookup to calculate - the second part of the table array is giving me problems:

Code:

---

=vlookup (Acct#,$AX:$AY,3,0)

---

X = MATCH(L9,A:A,0) - this works for me perfectly - gives me a number of where that text is (9 for example)
Y = MATCH(L9,A:A,0) or 9 PLUS the number of rows under A9 until a blank - for example 4, which would return 13 - which would define the changing array.


I cannot figure out a formula that tells it to start at A9 and count until the next blank row, and stop and return a number.


Example picture (first Building should say BUILDING 1):
Attachment 24389

Hello amandacipra,

Say your starting point is A9. You can use a Boolean array to find the first blank row and add that row number to the starting row number.

Code:

---

=MATCH(TRUE,($A$9:$A$100=""),0)+ROW($A$9)-1

---

Because this is an array formula, you must enter it using Ctrl+SHIFT+ENTER