Array Problem

[Jonas]

Hello,

I'm trying to create a function that will output the present value of
a stream of cash flows. I was able to get it to work for a range in a
column but I haven't been able to get it to work for a range in a
row. I tried to get rid of the transpose in various ways without any
luck. Below is the code that works for data in a column.

I think that it would also be neat if I could put something in the
code to determine if the stream of cash flows are in a column or in a
row. I was thinking that if I were able to determine the row using
Excel's row function of the beginning cell in the range and the the
row of the last cell in the range, I could create some logic to
determine if the cashflows are in a row or in a column.
------------------------------------------------------------------------------------------------------------------
Function pval_T(irate_T As Double, rngIn As Range) As Variant
Dim myArr As Variant, i As Long

myArr = Application.WorksheetFunction.Transpose(rngIn)

For i = LBound(myArr) To UBound(myArr)

pval_T = pval_T + myArr(i) / (1 + irate_T) ^ i

Next i

End Function

 

[Rick Rothstein \(MVP - VB\)]

Using your Range name, consider this structure...

If rngIn.Rows.Count > 1 Then
' Multiple rows only
Else rngIn.Columns.Count > 1 Then
' Multiple columns only
Else
' Multiple rows and columns
End If

Note the "s" on the end of the keyword Rows and Columns

Rick

 

[RB Smissaert]

Not sure you need the transpose and it is simple to see
from the resulting array what the range input was:

Sub test()

Dim arr

'row input range
arr = Range(Cells(1), Cells(10))
MsgBox UBound(arr), , UBound(arr, 2)

'column input range
arr = Range(Cells(1), Cells(10, 1))
MsgBox UBound(arr), , UBound(arr, 2)

End Sub


RBS

 

[Jonas]

Not sure you need the transpose and it is simple to see
from the resulting array what the range input was:

Sub test()

Dim arr

'row input range
arr = Range(Cells(1), Cells(10))
MsgBox UBound(arr), , UBound(arr, 2)

'column input range
arr = Range(Cells(1), Cells(10, 1))
MsgBox UBound(arr), , UBound(arr, 2)

End Sub

RBS













- Show quoted text -

This is good but I need to be able to have the number of cells in the
range be variable and dependent on the range that is chosen by the
user. Is there a way to convert a range to an array? The above code
does so by using "Transpose." I can't get the code to work if I just
type "myArr = rngIn." I need to find a way to put the values of the
range "rngIn" into an array.

 

[RB Smissaert]

The input can be variable. Still can't see why you need the transpose:

Sub test()

Dim rng
Dim arr

Set rng = Application.InputBox("Pick the required range, one column or one
row.", _
"", , Type:=8)

arr = rng
MsgBox UBound(arr), , UBound(arr, 2)

End Sub


RBS

Not sure you need the transpose and it is simple to see
from the resulting array what the range input was:

Sub test()

Dim arr

'row input range
arr = Range(Cells(1), Cells(10))
MsgBox UBound(arr), , UBound(arr, 2)

'column input range
arr = Range(Cells(1), Cells(10, 1))
MsgBox UBound(arr), , UBound(arr, 2)

End Sub

RBS













- Show quoted text -

This is good but I need to be able to have the number of cells in the
range be variable and dependent on the range that is chosen by the
user. Is there a way to convert a range to an array? The above code
does so by using "Transpose." I can't get the code to work if I just
type "myArr = rngIn." I need to find a way to put the values of the
range "rngIn" into an array.

 

[Rick Rothstein \(MVP - VB\)]

Does this code do what you want?

Function pval_T(irate_T As Double, rngIn As Range) As Variant
Dim i As Long
With rngIn
If rngIn.Rows.Count > 1 Then
For i = .Row To .Rows.Count - 1
pval_T = pval_T + .Cells(i, 1) / (1 + irate_T) ^ i
Next i
Else
For i = .Column To .Columns.Count - 1
pval_T = pval_T + .Cells(1, i) / (1 + irate_T) ^ i
Next i
End If
End With
End Function

It should loop through the cells of the passed-in (column oriented or row
oriented) range automatically.

Rick

Not sure you need the transpose and it is simple to see
from the resulting array what the range input was:

Sub test()

Dim arr

'row input range
arr = Range(Cells(1), Cells(10))
MsgBox UBound(arr), , UBound(arr, 2)

'column input range
arr = Range(Cells(1), Cells(10, 1))
MsgBox UBound(arr), , UBound(arr, 2)

End Sub

RBS













- Show quoted text -

This is good but I need to be able to have the number of cells in the
range be variable and dependent on the range that is chosen by the
user. Is there a way to convert a range to an array? The above code
does so by using "Transpose." I can't get the code to work if I just
type "myArr = rngIn." I need to find a way to put the values of the
range "rngIn" into an array.

 

[Jonas]

This is good but I need to be able to have the number of cells in the
range be variable and dependent on the range that is chosen by the
user. Is there a way to convert a range to an array? The above code
does so by using "Transpose." I can't get the code to work if I just
type "myArr = rngIn." I need to find a way to put the values of the
range "rngIn" into an array.- Hide quoted text -

- Show quoted text -

I was able to change the code. The only thing that I am lacking now
is the ability to count the number of cells in "rngIn." Below is the
new code. I want to replace 3 with the number of cells in the range
"rngIn."

-------------------------------------------------------------------------------------------------------------------------
Function pval_T(irate_T As Double, rngIn As Variant) As Variant
Dim myArr As Variant, i As Long


For i = 1 To 3


pval_T = pval_T + rngIn(i) / (1 + irate_T) ^ (i)

Next i

End Function

 

[Rick Rothstein \(MVP - VB\)]

I was able to change the code. The only thing that I am lacking

now is the ability to count the number of cells in "rngIn." Below
is the new code. I want to replace 3 with the number of cells in
the range "rngIn."

Did you try the code I posted earlier?

Rick

 

[Alan Beban]

Using your Range name, consider this structure...

If rngIn.Rows.Count > 1 Then
' Multiple rows only
Else rngIn.Columns.Count > 1 Then
' Multiple columns only
Else
' Multiple rows and columns
End If

Note the "s" on the end of the keyword Rows and Columns

Rick

It doesn't compile unless the first Else is changed to ElseIf; and then
it doesn't work; it shows a rectangular range (i.e., multiple rows and
columns) as multiple rows only.

Alan Beban

 

[Alan Beban]

Jonas wrote:. . . .

I was able to change the code. The only thing that I am lacking now
is the ability to count the number of cells in "rngIn." Below is the
new code. I want to replace 3 with the number of cells in the range
"rngIn."

-------------------------------------------------------------------------------------------------------------------------
Function pval_T(irate_T As Double, rngIn As Variant) As Variant
Dim myArr As Variant, i As Long


For i = 1 To 3


pval_T = pval_T + rngIn(i) / (1 + irate_T) ^ (i)

Next i

End Function

rngIn.Count

Alan Beban

 

[Rick Rothstein \(MVP - VB\)]

The Else instead of ElseIf was a typo. As for the other problem... good
catch! This should work...

If rngIn.Rows.Count > 1 and rngIn.Columns.Count > 1 Then
' Multiple rows and columns
Else rngIn.Columns.Count > 1 Then
' Multiple columns only
Else
' Multiple rows only
End If

Rick

 

[Rick Rothstein \(MVP - VB\)]

Alan Beban pointed out a problem with the "structure" I used in my first
posting in this thread (which I used in my subsequent coded postings here).
This is the modified code correcting for that structural problem...

Function pval_T(irate_T As Double, rngIn As Range) As Variant
Dim i As Long
With rngIn
If rngIn.Rows.Count > 1 And rngIn.Columns.Count > 1 Then
' Handle the Range-size error (multiple rows AND columns here)
Exit Sub
ElseIf rngIn.Rows.Count > 1 Then
For i = .Row To .Rows.Count - 1
pval_T = pval_T + .Cells(i, 1) / (1 + irate_T) ^ i
Next i
Else
For i = .Column To .Columns.Count - 1
pval_T = pval_T + .Cells(1, i) / (1 + irate_T) ^ i
Next i
End If
End With
End Function

Rick

 

[Dana DeLouis]

I may be wrong, but it appears to work if you pass the range either
vertically, or horizontally,

Sub Test()
Debug.Print WorksheetFunction.NPV(0.05, [A1:A3])
Debug.Print WorksheetFunction.NPV(0.05, [C1:E1])
End Sub

 

[Peter T]

I guess the 'Exit Sub' is a typo <g>

Apart from that, though I don't know what the function is supposed to do,
those For..To loops don't look right.

With rngIn
For i = .Row To .Rows.Count - 1

..Row returns row number of first row in rngIn, relative to topleft cell ?
presumably should loop from 1 to .Rows.Count

the i in .Cells(i,1) refers to row number relative to rngIn

If I (partially) follow, need to establish the input range is one row or
column, if it is could simply loop each cell in rngIn, eg

Function pval_Tb(irate_T As Double, rngIn As Range)
Dim i As Long
Dim dblTmp As Double
Dim cell As Range
On Error GoTo errExit
If rngIn.Rows.Count > 2 And rngIn.Columns.Count > 1 Then
pval_Tb = CVErr(xlErrRef)
Else
For Each cell In rngIn
i = i + 1 ' ???
dblTmp = dblTmp + cell.Value / (1 + irate_T) ^ i
Next
pval_Tb = dblTmp
End If
Exit Function
errExit:

pval_Tb = CVErr(xlValue)
End Function

I have no idea if the i = i + 1 is correct for the calculation, but I
imagine simple to adapt as necessary.

BTW is rngIn.Count = 1, a single cell, OK

Regards,
Peter T


PS to Jonas, in your OP you have

For i = LBound(myArr) To UBound(myArr)
pval_T = pval_T + myArr(i) / (1 + irate_T) ^ i

but myArr(i) will be a 2D array, even if only one row or column, so would
need
myArr(i, 1) or myArr(1, i) depending if vertical or horizontal

I looked at that trying to work out what the i should be, I assume it starts
at one ?

 

[RB Smissaert]

Not sure what is wrong with the code I suggested.
In simple words:
1. Assign the range to variant array
(this is the fastest way in any case, better than looping through the range)
2. Determine from the Ubound of the array if we have a row or a column
3. Loop through the array and do whatever needs doing.

RBS

 

[Rick Rothstein \(MVP - VB\)]

No, the Exit Sub was there on purpose. The Op seemed to indicate that only a
row of cells or a column of cells were a valid range, so if the count of the
rows and columns in the range are both greater than 1, I'm assuming the
range is not a valid one for the OP's purposes; hence the comment to "handle
the error" and, once it has been handled, exit the routine. As for the
For..To loops... they will either process all the cells in a range
consisting of cells in a column or a row (depending on the tested count
property). The row or column number of the first cell in a range of cells is
returned by the Row or Column property of the Range; hence the starting
value in the For..To statement. However, I did leave something out in the
ending value for the For..To loop... I forgot to add the starting cell's row
or column into the value I posted. Here is what the code should have been...

Function pval_T(irate_T As Double, rngIn As Range) As Variant
Dim i As Long
With rngIn
If rngIn.Rows.Count > 1 And rngIn.Columns.Count > 1 Then
' Handle the Range-size error (multiple rows AND columns here)
Exit Sub
ElseIf rngIn.Rows.Count > 1 Then
For i = .Row To .Row + .Rows.Count - 1
pval_T = pval_T + .Cells(i, .Column).Value / (1 + irate_T) ^ i
Next i
Else
For i = .Column To .Column + .Columns.Count - 1
pval_T = pval_T + .Cells(.Row, i).Value/ (1 + irate_T) ^ i
Next i
End If
End With
End Function

where the ending value for the For..To loop is calculated by adding the
starting row or column to the row or column cell count and subtracting 1. In
addition to **that** mistake, I had also screwed up the column number
reference inside the Cells property (correct in the above code). So, yes,
you were right... my For..To loops didn't "look right". Thanks for noting
that so I could correct my code.

Now, in thinking about your post, yes, I don't see why a For..Each loop
can't be used instead of a "counting" For-Next loop that I used (I kind of
got "mentally trapped" by the OP's attempt to use an array). As a matter of
fact, I think it is the better way to go. Just one note on your posted code
though; you have this statement....

If rngIn.Rows.Count > 2 And rngIn.Columns.Count > 1

I think the '2' should be a '1'.

Rick

 

[Jonas]

This is good but I need to be able to have the number of cells in the
range be variable and dependent on the range that is chosen by the
user. Is there a way to convert a range to an array? The above code
does so by using "Transpose." I can't get the code to work if I just
type "myArr = rngIn." I need to find a way to put the values of the
range "rngIn" into an array.- Hide quoted text -

- Show quoted text -

I was able redo the code(below). All I need now is a way to count the
number of cells in the range "rngIn" rather than use the static number
3 as is shown below.

-------------------------------------------------------------------------------------------------------------------------

Function pval_T(irate_T As Double, rngIn As Variant) As Variant
Dim myArr As Variant, i As Long


For i = 1 To 3

pval_T = pval_T + rngIn(i) / (1 + irate_T) ^ (i)

Next i

End Function

 

[Peter T]

In line -

I haven't studied the whole thread, so excuses and apologies in advance if
I'm out of order or plain wrong -

No, the Exit Sub was there on purpose.

Can't have Exit Sub in a Function, surely, what am I missing

The Op seemed to indicate that only a
row of cells or a column of cells were a valid range, so if the count of the
rows and columns in the range are both greater than 1, I'm assuming the
range is not a valid one for the OP's purposes; hence the comment to "handle
the error" and, once it has been handled, exit the routine. As for the
For..To loops... they will either process all the cells in a range
consisting of cells in a column or a row (depending on the tested count
property).

The row or column number of the first cell in a range of cells is
returned by the Row or Column property of the Range;

Agreed, but...

hence the starting
value in the For..To statement.

That would be fine if you went on to loop say ws.Cells(i, 1)
But you are looping cells qualified With rngIn

Set rngIn = Range("B10:B16")
With rngIn
Debug.Print .Row ' 10
Debug.Print .Cells(.Row, 1).Row ' 19 wrong
Debug.Print Cells(.Row, 1).Row ' 10 right
End With

similar impacts on the following

However, I did leave something out in the
ending value for the For..To loop... I forgot to add the starting cell's row
or column into the value I posted. Here is what the code should have been...

Function pval_T(irate_T As Double, rngIn As Range) As Variant
Dim i As Long
With rngIn
If rngIn.Rows.Count > 1 And rngIn.Columns.Count > 1 Then
' Handle the Range-size error (multiple rows AND columns here)
Exit Sub
ElseIf rngIn.Rows.Count > 1 Then
For i = .Row To .Row + .Rows.Count - 1
pval_T = pval_T + .Cells(i, .Column).Value / (1 + irate_T) ^ i
Next i
Else
For i = .Column To .Column + .Columns.Count - 1
pval_T = pval_T + .Cells(.Row, i).Value/ (1 + irate_T) ^ i
Next i
End If
End With
End Function

where the ending value for the For..To loop is calculated by adding the
starting row or column to the row or column cell count and subtracting 1. In
addition to **that** mistake, I had also screwed up the column number
reference inside the Cells property (correct in the above code). So, yes,
you were right... my For..To loops didn't "look right". Thanks for noting
that so I could correct my code.

Now, in thinking about your post, yes, I don't see why a For..Each loop
can't be used instead of a "counting" For-Next loop that I used (I kind of
got "mentally trapped" by the OP's attempt to use an array). As a matter of
fact, I think it is the better way to go. Just one note on your posted code
though; you have this statement....

If rngIn.Rows.Count > 2 And rngIn.Columns.Count > 1

I think the '2' should be a '1'.

Ooops, it was indeed a typo, thanks for correcting

FWIW I agree with RBS's comment that it's normally more efficient to assign
the range values to an array and work with that.

Regards,
Peter T

 

[Jonas]

In line -

I haven't studied the whole thread, so excuses and apologies in advance if
I'm out of order or plain wrong -


Can't have Exit Sub in a Function, surely, what am I missing




Agreed, but...


That would be fine if you went on to loop say ws.Cells(i, 1)
But you are looping cells qualified With rngIn

Set rngIn = Range("B10:B16")
With rngIn
Debug.Print .Row ' 10
Debug.Print .Cells(.Row, 1).Row ' 19 wrong
Debug.Print Cells(.Row, 1).Row ' 10 right
End With

similar impacts on the following












Ooops, it was indeed a typo, thanks for correcting

FWIW I agree with RBS's comment that it's normally more efficient to assign
the range values to an array and work with that.

Regards,
Peter T- Hide quoted text -

- Show quoted text -

Thank you all for responding to my post. I wasn't able to read them
earlier and didn't seem to be able to post anything either. I have
copied my solution below in case anybody wants to look at it. I'm
going to look at your posts tomorrow. I'm sure that there is some
good stuff in them; I have a lot to learn. I addition to what I have
below, I think that I will create a section of code that checks for
text in the range.

-------------------------------------------------------------------------------------------------------------------------
Function PvalCF_Tyrone(irate_T As Double, rngInty As Range) As Variant
''''''calculates the present value of a range of cash flows'''

Set myArr = rngInty

For Each Mycell In rngInty

counter = counter + 1

PvalCF_Tyrone = PvalCF_Tyrone + Mycell.Value / (1 + irate_T) ^ counter

Next Mycell
End Function

 

[Rick Rothstein \(MVP - VB\)]

Yep! I see what you meant now. Wow! I sure seem to have screwed this up
completely, didn't I? I didn't get a lot of sleep the night before, but I
thought I was more awake than my answer shows me to have been. Thanks for
picking up on all the problems in my response.

Rick