Loop problem through a range, UDF

[hfler]

Hi all,

I'm making a user defined function, where one of the inputs is a range. The range contains numbers that the UDF loops through and performs certain actions on. After a while, though, the range doesn't contain anymore numbers. For example,

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20 width=64>432</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20>3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20>23423</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20>-</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20>-</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20>-</TD></TR></TBODY></TABLE>


I'm trying to figure out how to select the last cell in the range that contains a number once the loop starts going into ranges that don't contain numbers. In a sub, I think I'd using something along the lines of,

Range(range_no_1).xlDown.Select

But I don't know how to do that in a UDF. Any help is much appreciated. Thanks!

 

[bubbapost]

Hello,

Quote:

Range(range_no_1).xlDown.Select

You might want to try this:

Sub LastCellInColumn()
Dim LastCell As Long
Dim ColNum As Long
 
ColNum = InputBox("Which Column?", "Last Cell In Column", Default)
LastCell = Cells(Rows.Count, ColNum).End(xlUp).Row
 
Cells(LastCell, ColNum).Select
 
End Sub

You can also put this part of the code within your own code:

Dim LastCell As Long
Dim ColNum As Long
 
ColNum = InputBox("Which Column?", "Last Cell In Column", Default)
LastCell = Cells(Rows.Count, ColNum).End(xlUp).Row

 

[hfler]

Thanks bubbapost,

That seems to work for a sub. For a UDF, though, how could you select the last cell with a number in it for a highlighted range? So for instance, I would have a formula structured like,

Function test(date as double, testvalue as double, variation as range)as variant

How could I loop through the variation range, and select the last cell in that range that contained a number?

Thanks again!

 

[shg]

You can't select a cell in a UDF. With some odd exceptions, all youu can do is return a value to the cell in which it appears.

 

[hfler]

Thanks shg