Address question

[scain2004]

Ok, I've got a function that returns the address of the cell. Usin
that address (say $D$306), I need to check for the first empty cell i
Cells(D307321). Any suggestions

 

[Frank Kabel]

Hi
do you need a worksheet function or some VBA code. For a
worksheetfunction you may use the following to return the
row number of your first blank cell (enter this formula as
array formula with CTRL+SHIFT+ENTER):
=306+MATCH(TRUE,ISBLANK(D307321),0)

 

[scain2004]

Actually, it needs to be done in VBA

 

[Frank Kabel]

Hi
one way: loop through the range. e.g.
....
dim rng as range
dim cell as range
set rng = range("D307321")
for each cell in rng
if cell.value="" then
msgbox cell.address
exit for
end if
next

 

[scain2004]

Ok, now how about this:

What I'm trying to do is when I find a date in my calendar worksheet,
say (12-May-04), I find the cell containing the date and the address is
returned. The address could be anywhere in columns A:G, but say I get
back ($D$401). Now the next 19 cells in column D (or whatever column
the date happens to be in) are the ones I have to search for an empty
cell (because some might already have data in them) and return the
address of the first empty cell in that range. And on top of all that,
the range is divided into two sections.

If type1 then search D402413
Elseif type2 then search D414421
End If

so the number of cells under each date is consistent, just need to
figure out how to reference those cells(rows) from the date cell.

 

[scain2004]

I figured it out, thank you