Address question

S

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(D307:D321). Any suggestions
 
F

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(D307:D321),0)
 
F

Frank Kabel

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

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 D402:D413
Elseif type2 then search D414:D421
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.
 
Top