Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
=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 |
#8
|
|||
|
|||
"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) |
#9
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Table Dilemma | Excel Worksheet Functions | |||
Copy one cell to entire column | Excel Discussion (Misc queries) | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
How do I reference every "n" cell in a column in Excel? | Excel Worksheet Functions | |||
In MS Excel, how do I fill in a column with the same cell from se. | Excel Worksheet Functions |