help locating first empty cell in a Column
 

All,

Let's assume I have a bunch of dates in column A. For Example:

A
1 10/02/03
2 11/15/04
3 12/31/04
4 01/04/03
5

I am looking for a function that will search column A and return the
address of the first blank cell (in this example A5). I have been
trying to do this with an Address & Match combo, but am having no
luck.

Any help is very much appreciated!
Thanks.


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=468069

Try this:

=ADDRESS(MATCH(9.99999999999999E+307,A:A)+1,1)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Celt" wrote in message
...

All,

Let's assume I have a bunch of dates in column A. For Example:

A
1 10/02/03
2 11/15/04
3 12/31/04
4 01/04/03
5

I am looking for a function that will search column A and return the
address of the first blank cell (in this example A5). I have been
trying to do this with an Address & Match combo, but am having no
luck.

Any help is very much appreciated!
Thanks.


--
Celt
------------------------------------------------------------------------
Celt's Profile:
http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=468069

If you are doing this for information gathering only, so you can see
where you are,
in cell B1
="A"&(count(A:A)+1)
But that data will not be usable in a formula, just info.


--
flydecoder
------------------------------------------------------------------------
flydecoder's Profile: http://www.excelforum.com/member.php...o&userid=27288
View this thread: http://www.excelforum.com/showthread...hreadid=468069

Hi!

in cell B1
="A"&(count(A:A)+1)
But that data will not be usable in a formula, just info.

Because the formula resolves to a cell reference, you could use Indirect to
reference B1.

Biff

"flydecoder" wrote
in message ...

If you are doing this for information gathering only, so you can see
where you are,
in cell B1
="A"&(count(A:A)+1)
But that data will not be usable in a formula, just info.


--
flydecoder
------------------------------------------------------------------------
flydecoder's Profile:
http://www.excelforum.com/member.php...o&userid=27288
View this thread: http://www.excelforum.com/showthread...hreadid=468069

Thanks for all the help guys!

Question for RagDyer:

Can I use this formula in a Macro?

here is what I have:

ActiveWorkbook.Names.Add Name:="ABC", RefersToR1C1:= _

"=OFFSET(INDIRECT(ADDRESS(MATCH(""EML*"",Sheet1!C6 ,0),6)),0,-5,COUNTIF(Sheet1!C6,LEFT(""EML*"",5)),COUNTA(Sheet 1!R4))"
ActiveWorkbook.Names.Add Name:="EMPTY", RefersToR1C1:= _
"=ADDRESS(MATCH(9.99999999999999E+307,Sheet1!R1C1) +1,1,4)"
Range("ABC").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A4").Select
ActiveSheet.paste
Range("EMPTY").Select

What I want this to do, is copy a chunk of specific data (identified as
containing the account EML) from Sheet1 and paste it to Sheet2. That
part works great.

I then wanted the macro to select the first empty cell on Sheet2 below
the data I just pasted........in order for me to paste more stuff.

Visual Basic doesn't like my "EMPTY" reference. Am I doing something
wrong here?


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=468069

Once you start talking code, I'm out of your league.

If you don't get an answer in a day or so, post to the programming group.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Celt" wrote in message
...

Thanks for all the help guys!

Question for RagDyer:

Can I use this formula in a Macro?

here is what I have:

ActiveWorkbook.Names.Add Name:="ABC", RefersToR1C1:= _


"=OFFSET(INDIRECT(ADDRESS(MATCH(""EML*"",Sheet1!C6 ,0),6)),0,-5,COUNTIF(Sheet
1!C6,LEFT(""EML*"",5)),COUNTA(Sheet1!R4))"
ActiveWorkbook.Names.Add Name:="EMPTY", RefersToR1C1:= _
"=ADDRESS(MATCH(9.99999999999999E+307,Sheet1!R1C1) +1,1,4)"
Range("ABC").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A4").Select
ActiveSheet.paste
Range("EMPTY").Select

What I want this to do, is copy a chunk of specific data (identified as
containing the account EML) from Sheet1 and paste it to Sheet2. That
part works great.

I then wanted the macro to select the first empty cell on Sheet2 below
the data I just pasted........in order for me to paste more stuff.

Visual Basic doesn't like my "EMPTY" reference. Am I doing something
wrong here?


--
Celt
------------------------------------------------------------------------
Celt's Profile:
http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=468069

=ADDRESS(MATCH(TRUE,A3:A15="",0)+ROWS(A1:A2),COLUM N(A3:A15),4)

This array formula is a bit funky because the MATCH function returns
the position, not the row# from the range A3:A15, meaning A3 is
position 1, A4 is position 2, A5 position 3, ECT. You have to add the
two rows not included in the range,
+ROWS(A1:A2), this will offset the positioning of the MATCH function
giving the true row#, without adding +2 for a blank cell in A5 the
formula would return A3 instead.

Hope this helps.

Matt


--
Flintstone
------------------------------------------------------------------------
Flintstone's Profile: http://www.excelforum.com/member.php...o&userid=15310
View this thread: http://www.excelforum.com/showthread...hreadid=468069

"Celt" wrote...
....
ActiveWorkbook.Names.Add Name:="ABC", RefersToR1C1:= _
"=OFFSET(INDIRECT(ADDRESS(MATCH(""EML*"",Sheet1!C 6,0),6)),0,-5,
COUNTIF(Sheet1!C6,LEFT(""EML*"",5)),COUNTA(Sheet1 !R4))"

INDIRECT(ADDRESS(...)) is always a BAD idiom, and when it's the 1st argument
to OFFSET it's even worse. What you want is the range beginning in column A
and the row in column F (6) matching "EML*" and spanning COUNTIF(...) rows
and COUNTA(...) columns. Just use

=OFFSET(R1C1,MATCH("EML*",Sheet1!C6,0)-1,0,
COUNTIF(Sheet1!C6,"EML*"),COUNTA(Sheet1!R4))

Note that LEFT("EML*",5) returns "EML*", so no point to the LEFT call.

ActiveWorkbook.Names.Add Name:="EMPTY", RefersToR1C1:= _
"=ADDRESS(MATCH(9.99999999999999E+307,Sheet1!R1C1 )+1,1,4)"

The match call is searching a SINGLE cell, R1C1 (aka, A1), so the result can
only be 1 if the cell is numeric and #N/A otherwise. There are easier ways
to do this, like

=IF(ISNUMBER(Sheet1!R1C1),"A2",#N/A)

Thanks Harlan!

That "IF(ISNUMBER()" function you gave me won't work for me, though.

=IF(ISNUMBER(Sheet1!R1C1),"A2",#N/A) will always return A2 if there is
a number in A1. Essentially, what I was looking for was a function
that would search all of column A (A:A) and give me the ADDRESS of the
next empty cell after the last piece of data in that column. So lets
say the last bit of data is in A14, I want the function to return
A15...the next empty cell.

The first 2 rows of column A contain no data. One of the earlier
functions given works perfectly...

=ADDRESS(MATCH(9.99999999999999E+307,Sheet1!A:A)+ 1,1,4)

This gives me the what I am looking for, but my macro doesn't like it.
Alone it works fine, in the macro I get an error.

I think it has to do with how A:A translates into R1C1......

There has got to be a way to do this.

Thanks for all the help so far!


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=468069