help locating first empty cell in a Column

**Celt** · 09-15-2005, 07:31 PM

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.

**flydecoder** · 09-15-2005, 09:39 PM

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.

RagDyer · 09-15-2005, 10:05 PM

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

Biff · 09-16-2005, 12:05 AM

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

**Celt** · 09-16-2005, 12:33 PM

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 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?

RagDyer · 09-16-2005, 04:05 PM

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

**Flintstone** · 09-16-2005, 04:56 PM

=ADDRESS(MATCH(TRUE,A3:A15="",0)+ROWS(A1:A2),COLUMN(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

Harlan Grove · 09-16-2005, 10:05 PM

"Celt" wrote...
....
>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))"

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)

**Celt** · 09-19-2005, 11:58 AM

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!