help locating first empty cell in a Column

C

Celt

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.
 
R

RagDyer

Try this:

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

--
HTH,

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

flydecoder

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.
 
B

Biff

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

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

Biff
 
C

Celt

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(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 a
containing the account EML) from Sheet1 and paste it to Sheet2. Tha
part works great.

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

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

RagDyer

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.
 
F

Flintstone

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

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

Hope this helps.

Mat
 
H

Harlan Grove

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

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)"
Click to expand...

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)
 
C

Celt

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 i
a number in A1. Essentially, what I was looking for was a functio
that would search all of column A (A:A) and give me the ADDRESS of th
next empty cell after the last piece of data in that column. So let
say the last bit of data is in A14, I want the function to retur
A15...the next empty cell.

The first 2 rows of column A contain no data. One of the earlie
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
 
Top