UDF vs Sub For loop

[hfler]

I have a question regarding a Loop in a UDF. In a Sub, I would normally use a For loop so it appeared,

For i=0 to 10
IF something THEN something
ELSE Something
Next i

In a UDF that has a range as an input though, I want to loop through the input range. I've seen example that look like,

Function test(practice as range)
Dim pr as range
 
For Each pr in practice
IF something THEN something
ELSE something
Next pr

But if I don't want to look at each "pr" in the range practice (i.e. I only want the loop to go through 10 times), how can I limit the number of time the loop circulates?

Thanks!

 

[JoeMo]

The range that is the input is rectangular, say r Rows deep by c columns wide. Suppose r > 10 and c > 5, but you only want to loop through the first 10 rows and 5 columns:

Function test(rng As Range)

For i = 1 To 10
    For j = 1 To 5
        With rng.Cells(i, j)
            'your code
        End With
    Next j
Next i
test = 'output you want from the function here
End Function

 

[hfler]

Thanks very much for the suggestion JoeMo. That definitely answers my question. I've realized putting the loop within the loop doesn't really accomplish my goal, though.

I think what I need to do is have the range as the same rectangle, but only loop the i's - and then multiply each i by the corresponding j in the adjacent column in the range... So I think it would be something like,

For each j=1 to 10
output = j * i

Where i is the value in the column directly adjacent to the j column in the range. Only I don't know how to set the column adjacent to current j as an i, to get this result? Any help is much appreciated. Thanks!

 

[JoeMo]

Thanks very much for the suggestion JoeMo. That definitely answers my question. I've realized putting the loop within the loop doesn't really accomplish my goal, though.

I think what I need to do is have the range as the same rectangle, but only loop the i's - and then multiply each i by the corresponding j in the adjacent column in the range... So I think it would be something like,

For each j=1 to 10
output = j * i

Where i is the value in the column directly adjacent to the j column in the range. Only I don't know how to set the column adjacent to current j as an i, to get this result? Any help is much appreciated. Thanks!

Say the range is in Columns A & B. Then Cells(i,1) are in Column A (the second index "1" indicates Column 1 which is "A") and Cells(i,2) are the companion cells in the 2nd column ("B"). So:

For i = 1 to 10
Output = Cells(i,1)*Cells(i,2)
Cells(i,3) = Output
Next i

Puts the product of the adjacent cells in A & B into the adjacent cell in column C.

 

[hfler]

JoeMo,

Thanks for your reply, I think this is very close to working as I no longer receive "#VALUE!" on my formula. Here's what I have,

Function test(startdate As Double, enddate As Double, expire As Range, prices As Range) As Variant
 
.variable assignments...
.
.
.
.
.
 
currmo = startdate
    For Each j In expire
        pxjump = (currspread * prices(j)) + px
        px = pxjump
    Next j
 
test = px

Here are my columns,

<TABLE style="WIDTH: 259pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=345><COLGROUP><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 71pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 height=20 width=95>Start</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=77>End</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 width=92>Expiration</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 width=81>Price</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=xl68 height=20>4/1/2011</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>5/31/2012</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>4/29/2011</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70>731</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" height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>6/30/2011</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70>739</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" height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>8/31/2011</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70>697</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" height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>11/30/2011</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70>655</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" height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>2/29/2012</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70>663</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" height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>4/30/2012</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70>670</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" height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>6/29/2012</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70>671</TD></TR></TBODY></TABLE>
Why do you think it's returning zero? Thanks again for your help!

 

[JoeMo]

Your syntax is wrong. If prices is a range that is only one row or one column you could have something like "prices.cells(j)" to represent the value of the jth cell in the range prices. "For each j in expire" where expire is a range implies that j is a cell in the range which is itself a range object whereas I think you want j to be an integer. If you could post all of your code and tell me what you want it to accomplish, I might be able to give you more insight.

 

[hfler]

JoeMo,

I'll post my entire code later this morning - it's currently on my jump which I don't have. The end goal is to get a weighted-average price. Thanks for your last suggestion, I've modified the code and it works - the problem I'm running into now is I can't loop From j=0, in order to select the first cell in the range. How can I select that first cell if j can't start from 0?

Thanks again!