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
---------------------------------------------------------------------------------------------------------------------
Suggestions are appreciated.

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


"Jonas" wrote in message
ups.com...
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
---------------------------------------------------------------------------------------------------------------------
Suggestions are appreciated.

Rick Rothstein (MVP - VB) wrote:
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

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


"Alan Beban" wrote in message
...
Rick Rothstein (MVP - VB) wrote:
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

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" wrote in message
ups.com...
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
---------------------------------------------------------------------------------------------------------------------
Suggestions are appreciated.

On Oct 17, 10:10 am, "RB Smissaert"
wrote:
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" wrote in message

ups.com...



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
---------------------------------------------------------------------------*------------------------------------------
Suggestions are appreciated.- Hide quoted text -

- 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.

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


"Jonas" wrote in message
ps.com...
On Oct 17, 10:10 am, "RB Smissaert"
wrote:
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" wrote in message

ups.com...



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
---------------------------------------------------------------------------*------------------------------------------
Suggestions are appreciated.- Hide quoted text -

- 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.

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


"Jonas" wrote in message
ps.com...
On Oct 17, 10:10 am, "RB Smissaert"
wrote:
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" wrote in message

ups.com...



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
---------------------------------------------------------------------------*------------------------------------------
Suggestions are appreciated.- Hide quoted text -

- 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.

On Oct 17, 10:30 am, Jonas wrote:
On Oct 17, 10:10 am, "RB Smissaert"
wrote:





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" wrote in message

oups.com...

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
---------------------------------------------------------------------------**------------------------------------------
Suggestions are appreciated.- Hide quoted text -

- 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.- 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

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 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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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

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

On Oct 17, 10:30 am, Jonas wrote:
On Oct 17, 10:10 am, "RB Smissaert"
wrote:





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" wrote in message

oups.com...

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
---------------------------------------------------------------------------**------------------------------------------
Suggestions are appreciated.- Hide quoted text -

- 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.- 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

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

--
Dana DeLouis



"Jonas" wrote in message
ups.com...
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
---------------------------------------------------------------------------------------------------------------------
Suggestions are appreciated.