General discussion
Thread display: Collapse - |
All Comments
Start or search
Create a new discussion
If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.
Related Discussions
-
12
-
23
-
0
-
10
-
3
Excel UDF Help - "Select Case"
single row or column), method (1-4), and an optional rate (-1 <= rate <= 1). The four methods are: 1) last value in range, 2) last value minus
first value divided by number of intervals, 3) same as (1) but multiplied by 1 plus rate, and 4) same as (2) but multiplied by rate. I am trying
to use "Select Case" vs. multiple If...Then...Else. When I use the formulas between the "Case" sections, they work fine. But when I tie them all
together using "Select Case," I get the #VALUE! error.
Please help me find the error in my ways. Thank you in advance!
Rob
Public Function rForecast(fRange As Range, _
fType As Integer, _
Optional fRate As Double) As Double
Application.Volatile True
' Declare variables
Dim CtRows As Long
Dim CtCols As Long
Dim RngChk As Integer
RngChk = 0
Dim fRangeFirstRow As Integer
Dim fRangeFirstCol As Integer
Dim fRangeLastRow As Integer
Dim fRangeLastCol As Integer
' Check for valid arguments
CtRows = fRange.Rows.Count
CtCols = fRange.Columns.Count
If CtRows > 1 Then RngChk = RngChk + 1 ' Allow ranges in one row or
If CtCols > 1 Then RngChk = RngChk + 1 ' in one column only
If fRate = "" Then fRate = 0 ' Assume rate is 0 if left blank
' Get values of first and last rows and columns in range
fRangeFirstRow = fRange.Row
fRangeFirstCol = fRange.Column
fRangeLastRow = fRange.Row + fRange.Rows.Count - 1
fRangeLastCol = fRange.Column + fRange.Columns.Count - 1
' Calculate function
If RngChk <= 1 Then
Select Case fType
Case 1
rForecast = Cells(fRangeLastRow, fRangeLastCol).Value
Case 2
rForecast = Cells(fRangeLastRow, fRangeLastCol).Value + _
(Cells(fRangeLastRow, fRangeLastCol).Value - Cells(fRangeFirstRow, fRangeFirstCol).Value) _
/ (WorksheetFunction.Count(fRange) - 1)
Case 3
rForecast = Cells(fRangeLastRow, fRangeLastCol).Value * (1 + fRate)
Case 4
rForecast = fRate * (Cells(fRangeLastRow, fRangeLastCol).Value + _
(Cells(fRangeLastRow, fRangeLastCol).Value - Cells(fRangeFirstRow, fRangeFirstCol).Value) _
/ (WorksheetFunction.Count(fRange) - 1))
Case Else
rForecast = CVErr(xlErrNum)
End Select
End If
End Function
This conversation is currently closed to new comments.