WorksheetFunction.Trend doesn't work?
 

Folks,

I can't seem to get this simple function to work

I keep getting an "Unable to get Trend Properties of the Worksheet Function
Class" error. Trend is on the supported Application.WorksheetFunction list.
Perhaps the Trend function is not supported by VBA - or am I just making a
foolish code error?

Below is the Code. On the active sheet, the range J11:U11 contains product
sales numbers between 5.05 and 9.30 (in thousands) and range J4:U4 contains
the numbers 1 to 12.

Thanks for your help.

Allan P. London, CPA

__________________________________________________ _
Sub FunctionTest()
MsgBox TF(11, 13)
End Sub
__________________________________________________ _
Function TF(theRow As Integer, theCol As Integer) As Double

Dim I As Integer
Dim theRange As String
Dim YRange As Range
Dim XRange As Range

theRange = "J" & theRow & ":U" & theRow
MsgBox (theRow & " " & theCol & " " & theRange)
Set YRange = ActiveSheet.Range(theRange)
Set XRange = ActiveSheet.Range("J4:U4")
TF = Application.WorksheetFunction.Trend(YRange, XRange, theCol)

End Function

WorksheetFunction.Trend doesn't work?
 

Sub FunctionTest()
Dim i As Integer
Dim v As Variant
Dim v1 As Variant
ReDim v(1 To 1)
v(1) = 13
i = 11
v1 = TF(i, v)
For i = LBound(v1) To UBound(v1)
Debug.Print i, v1(i)
Next
End Sub

Function TF(theRow As Integer, theCol As Variant)

Dim i As Integer
Dim theRange As String
Dim YRange As Range
Dim XRange As Range

theRange = "J" & theRow & ":U" & theRow
'MsgBox (theRow & " " & theCol & " " & theRange)
Set YRange = ActiveSheet.Range(theRange)
Set XRange = ActiveSheet.Range("J4:U4")
TF = Application.WorksheetFunction.Trend(YRange, XRange, theCol)

End Function


--
Regards,
Tom Ogilvy

"alondon" wrote in message
...
Folks,

I can't seem to get this simple function to work

I keep getting an "Unable to get Trend Properties of the Worksheet
Function Class" error. Trend is on the supported
Application.WorksheetFunction list. Perhaps the Trend function is not
supported by VBA - or am I just making a foolish code error?

Below is the Code. On the active sheet, the range J11:U11 contains
product sales numbers between 5.05 and 9.30 (in thousands) and range J4:U4
contains the numbers 1 to 12.

Thanks for your help.

Allan P. London, CPA

__________________________________________________ _
Sub FunctionTest()
MsgBox TF(11, 13)
End Sub
__________________________________________________ _
Function TF(theRow As Integer, theCol As Integer) As Double

Dim I As Integer
Dim theRange As String
Dim YRange As Range
Dim XRange As Range

theRange = "J" & theRow & ":U" & theRow
MsgBox (theRow & " " & theCol & " " & theRange)
Set YRange = ActiveSheet.Range(theRange)
Set XRange = ActiveSheet.Range("J4:U4")
TF = Application.WorksheetFunction.Trend(YRange, XRange, theCol)

End Function

WorksheetFunction.Trend doesn't work?
 

Tom,
Got it, I have to feed Trend only arrays, won't work with single value.
And, it returns only an array, not a single value. Thanks APL

"Tom Ogilvy" wrote in message
...
Sub FunctionTest()
Dim i As Integer
Dim v As Variant
Dim v1 As Variant
ReDim v(1 To 1)
v(1) = 13
i = 11
v1 = TF(i, v)
For i = LBound(v1) To UBound(v1)
Debug.Print i, v1(i)
Next
End Sub

Function TF(theRow As Integer, theCol As Variant)

Dim i As Integer
Dim theRange As String
Dim YRange As Range
Dim XRange As Range

theRange = "J" & theRow & ":U" & theRow
'MsgBox (theRow & " " & theCol & " " & theRange)
Set YRange = ActiveSheet.Range(theRange)
Set XRange = ActiveSheet.Range("J4:U4")
TF = Application.WorksheetFunction.Trend(YRange, XRange, theCol)

End Function


--
Regards,
Tom Ogilvy

"alondon" wrote in message
...
Folks,

I can't seem to get this simple function to work

I keep getting an "Unable to get Trend Properties of the Worksheet
Function Class" error. Trend is on the supported
Application.WorksheetFunction list. Perhaps the Trend function is not
supported by VBA - or am I just making a foolish code error?

Below is the Code. On the active sheet, the range J11:U11 contains
product sales numbers between 5.05 and 9.30 (in thousands) and range
J4:U4 contains the numbers 1 to 12.

Thanks for your help.

Allan P. London, CPA

__________________________________________________ _
Sub FunctionTest()
MsgBox TF(11, 13)
End Sub
__________________________________________________ _
Function TF(theRow As Integer, theCol As Integer) As Double

Dim I As Integer
Dim theRange As String
Dim YRange As Range
Dim XRange As Range

theRange = "J" & theRow & ":U" & theRow
MsgBox (theRow & " " & theCol & " " & theRange)
Set YRange = ActiveSheet.Range(theRange)
Set XRange = ActiveSheet.Range("J4:U4")
TF = Application.WorksheetFunction.Trend(YRange, XRange, theCol)

End Function