Locating Cell Number of first occurance of data in Column
 

Hello Folks,

I WILL GIVE THE FIRST PERSON TO ANSWER
THIS QUESTION IN A MANNER THAT ACTUALLY
HELPS ME A FREE FOOTBAG (HACKY SACK)
FOR HELPING ME! Your choice of colors:

http://www.freedomfootbags.com/shop/...g-footbag.html


This is probably easy to do, but I'm totally stuck right now...
I have columns of data that are 99.8% blank (technically,
they're not blank, they have been assigned ""), and
I'm trying to determine the first cell that has useful info
in it. For example Column AN has:

1.MISC STUFF
2.MISC STUFF
3.""
4.""
5.""
6.""
7.""
8.USEFUL DATA
9.USEFUL DATA
10.""
11.""
12.""
13.
14.

I'm trying to come up with a simple function that will tell me the
first row that has "useful data" in it based on a range search.
If the function was called "VERYSMART", you should be able
to go "VERYSMART(AN3:AN100)" and the result should be "8"

I've been working with the LASTINCOLUMN function:
--------------------------------
Function LASTINCOLUMN(rngInput As Range)
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Application.Volatile
Set WorkRange = rngInput.Columns(1).EntireColumn
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
LASTINCOLUMN = WorkRange(i).Value
Exit Function
End If
Next i
End Function
-------------------------------

Now, LASINCOLUMN is a good starting point (I think),
but my Visual Basic skills are poor at best, and
I'm not sure where to go... For example, my cells
that contain "" are the result of a formula, so they
are NOT empty, and LASTINCOLUM is not helping me.

Thanks in advance for your help! Peace.

Daryl "Genzu Blades" Genz
Owner, Freedom Footbags
1998, 2000, 2001, 2002 Doubles Freestyle Footbag World Champ
http://www.freedomfootbags.com

Toll Free 1.866.KICKFREE (542.5373) 720.887.8226 (in Colorado

--
Message posted from http://www.ExcelForum.com

Locating Cell Number of first occurance of data in Column
 

Hi
do you really need VBA for this?
a worksheet function could be the following: enter this as array
formula with CTRL+SHIFT+ENTER:
=MIN(IF(AN3:AN100<"",ROW(AN3:AN100))

if you need VBA, the following could do
Public Function get_useful_column(rng as range)
Dim cell as range
Dim ret_value
ret_value=0
for each cell in rng
if cell.value<"" then
ret_value=cell.row
exit for
end if
next
if ret_value=0 then
get_useful_column=CVEr(xlErrValue)
else
get_useful_column=ret_value
end if
end function


--
Regards
Frank Kabel
Frankfurt, Germany


Hello Folks,

I WILL GIVE THE FIRST PERSON TO ANSWER
THIS QUESTION IN A MANNER THAT ACTUALLY
HELPS ME A FREE FOOTBAG (HACKY SACK)
FOR HELPING ME! Your choice of colors:

http://www.freedomfootbags.com/shop/...g-footbag.html


This is probably easy to do, but I'm totally stuck right now...
I have columns of data that are 99.8% blank (technically,
they're not blank, they have been assigned ""), and
I'm trying to determine the first cell that has useful info
in it. For example Column AN has:

1.MISC STUFF
2.MISC STUFF
3.""
4.""
5.""
6.""
7.""
8.USEFUL DATA
9.USEFUL DATA
10.""
11.""
12.""
13.
14.

I'm trying to come up with a simple function that will tell me the
first row that has "useful data" in it based on a range search.
If the function was called "VERYSMART", you should be able
to go "VERYSMART(AN3:AN100)" and the result should be "8"

I've been working with the LASTINCOLUMN function:
--------------------------------
Function LASTINCOLUMN(rngInput As Range)
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Application.Volatile
Set WorkRange = rngInput.Columns(1).EntireColumn
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
LASTINCOLUMN = WorkRange(i).Value
Exit Function
End If
Next i
End Function
-------------------------------

Now, LASINCOLUMN is a good starting point (I think),
but my Visual Basic skills are poor at best, and
I'm not sure where to go... For example, my cells
that contain "" are the result of a formula, so they
are NOT empty, and LASTINCOLUM is not helping me.

Thanks in advance for your help! Peace.

Daryl "Genzu Blades" Genz
Owner, Freedom Footbags
1998, 2000, 2001, 2002 Doubles Freestyle Footbag World Champ
http://www.freedomfootbags.com

Toll Free 1.866.KICKFREE (542.5373) 720.887.8226 (in Colorado)


---
Message posted from http://www.ExcelForum.com/

Locating Cell Number of first occurance of data in Column
 

Hello Frank!

Thanks very much...
While the first formula looks nice, it
keeps giving me an error: "#VALUE!"
And I am not exactly sure why,
nevertheless, your second approach
worked (nearly) perfectly, with the
exception of a missing "r" after CVEr:

"get_useful_column=CVEr(xlErrValue)"

You win a free footbag, and I sincerely
appreciate the help :).

Peace.


---
Message posted from http://www.ExcelForum.com/

Locating Cell Number of first occurance of data in Column
 

Hi
for the formula you have to enter it as array formula:
That is hit CTRL+SHIFT+ENTER after entering the formula
instead of a single ENTER

-----Original Message-----
Hello Frank!

Thanks very much...
While the first formula looks nice, it
keeps giving me an error: "#VALUE!"
And I am not exactly sure why,
nevertheless, your second approach
worked (nearly) perfectly, with the
exception of a missing "r" after CVEr:

"get_useful_column=CVEr(xlErrValue)"

You win a free footbag, and I sincerely
appreciate the help :).

Peace.


---
Message posted from http://www.ExcelForum.com/

.

Locating Cell Number of first occurance of data in Column
 

Frank Kabel wrote:[color=blue]
[b]Hi
for the formula you have to enter it as array formula:
That is hit CTRL+SHIFT+ENTER after entering the formula
instead of a single ENTER


Ah, okay. Never heard of such a thing before.
Thanks, yet again. :) Please contact me personally
about getting your footbag since you don't have
an account here (and I can't PM you).

Peace

--
Message posted from http://www.ExcelForum.com

Locating Cell Number of first occurance of data in Column
 

Hi
no need for sending me the footbag (probably the sipping costs to
Germany would exceed the costs of it).
So just thanks for your thanks :-)

--
Regards
Frank Kabel
Frankfurt, Germany

[color=blue]
Frank Kabel wrote:
[b]Hi
for the formula you have to enter it as array formula:
That is hit CTRL+SHIFT+ENTER after entering the formula
instead of a single ENTER


Ah, okay. Never heard of such a thing before.
Thanks, yet again. :) Please contact me personally
about getting your footbag since you don't have
an account here (and I can't PM you).

Peace.


---
Message posted from http://www.ExcelForum.com/

Locating Cell Number of first occurance of data in Column
 

Hello Frank,

It only costs me $1.60 to send a footbag and
in the time you have saved me I could have made
several bags :). I'm more than happy to send
it if you are interested. Thanks again for your
help. Peace.

Genz

--
Message posted from http://www.ExcelForum.com