Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax in worksheet functions for individual values instead of ranges
I am trying to use the TREND function in a macro but cannot work out
the syntax. I only need a single value result and can get the following to put the correct value into the active cell: ActiveCell.FormulaR1C1 = "=TREND({5;10},{10;20},15)" But dont know how to evaluate this to a variable, without using a cell. Ideally I would like the numbers to be variables also. Any help gratefully received. Rod |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax in worksheet functions for individual values instead of ranges
On Sat, 8 Oct 2011 12:39:37 -0700 (PDT), REM wrote:
I am trying to use the TREND function in a macro but cannot work out the syntax. I only need a single value result and can get the following to put the correct value into the active cell: ActiveCell.FormulaR1C1 = "=TREND({5;10},{10;20},15)" But dont know how to evaluate this to a variable, without using a cell. Ideally I would like the numbers to be variables also. Any help gratefully received. Rod I'm not sure exactly what you want, but perhaps: Dim v As Variant v = Evaluate("=TREND({5;10},{10;20},15)") Debug.Print v(1) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax in worksheet functions for individual values instead of ranges
On Sat, 8 Oct 2011 12:39:37 -0700 (PDT), REM wrote:
I am trying to use the TREND function in a macro but cannot work out the syntax. I only need a single value result and can get the following to put the correct value into the active cell: ActiveCell.FormulaR1C1 = "=TREND({5;10},{10;20},15)" But dont know how to evaluate this to a variable, without using a cell. Ideally I would like the numbers to be variables also. Any help gratefully received. Rod Another approach: Dim v As Variant v = Application.WorksheetFunction.Trend(Array(5, 10), Array(10, 20), Array(15)) Debug.Print v(1) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax in worksheet functions for individual values instead of ranges
"REM" wrote:
I am trying to use the TREND function in a macro but cannot work out the syntax. [....] ActiveCell.FormulaR1C1 = "=TREND({5;10},{10;20},15)" Works fine for me in XL2003 with VBA 6.5. That is, when an cell is indeed selected. I wonder if you selected another object (e.g. a chart) at the time you executed that line in the macro. REM wrote: dont know how to evaluate this to a variable, without using a cell. y = Evaluate("=TREND({5;10},{10;20},15)") or [1]: y = WorksheetFunction.Forecast(15, Array(5,10), Array(10,20)) REM wrote: Ideally I would like the numbers to be variables also. knownY = Array(5,10) knownX = Array(10,20) x = 15 y = WorksheetFunction.Forecast(x,knownY,knownX) ----- [1] I don't know why one of the following does not work. I don't care because I would use FORECAST for this purpose instead of TREND anyway. y = WorksheetFunction.Trend(Array(5,10), Array(10,20), 15) y = WorksheetFunction.Trend(Array(5,10), Array(10,20), Array15)) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax in worksheet functions for individual values instead of ranges
I wrote:
[1] I don't know why one of the following does not work. I don't care because I would use FORECAST for this purpose instead of TREND anyway. y = WorksheetFunction.Trend(Array(5,10), Array(10,20), 15) y = WorksheetFunction.Trend(Array(5,10), Array(10,20), Array15)) Aha! Looking at Ron's response, I know now why they did not work. The first does not work because apparently WorksheetFunction.Trend requires that the 3rd parameter be an array. I had guessed as much, which is why I tried the second form. The second form actually did work. I did not realize that the error I got was not on that statement, but on my next statement: Msgbox y. That did not work because apparently WorksheetFunction.Trend returns an array. So I should have written Msgbox y(1), as Ron did. All the more reason to use FORECAST and WorksheetFunction.Forecast for this purpose (returning a single value). |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax in worksheet functions for individual values instead of ranges
PS.... I wrote:
[1] I don't know why one of the following does not work. I don't care because I would use FORECAST for this purpose instead of TREND anyway. y = WorksheetFunction.Trend(Array(5,10), Array(10,20), 15) y = WorksheetFunction.Trend(Array(5,10), Array(10,20), Array15)) Aha! Looking at Ron's response, I know now why they did not work. [....] The second form actually did work. I did not realize that the error I got was not on that statement, but on my next statement: Msgbox y. Likewise, if you use: y = Evaluate("=TREND({5;10},{10;20},15)") you need to reference y(1) for the single value. Again, I would prefer FORECAST to return a single value, to wit: y = Evaluate("=FORECAST(15,{5;10},{10;20})") MsgBox y |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax in worksheet functions for individual values instead of ranges
PS2.... I wrote:
"joeu2004" wrote in message ... REM wrote: Ideally I would like the numbers to be variables also. knownY = Array(5,10) knownX = Array(10,20) x = 15 y = WorksheetFunction.Forecast(x,knownY,knownX) Or: y = WorksheetFunction.Forecast(x,Array(y1,y2),Array(x1 ,x2)) MsgBox y If you still cannot figure it, provide the relevant VBA context -- declaration of variables and code fragment showing how those variables are set up. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax in worksheet functions for individual values instead of ranges
On Oct 8, 9:44*pm, "joeu2004" wrote:
PS2.... *I wrote: "joeu2004" wrote in message ... REM wrote: Ideally I would like the numbers to be variables also. knownY = Array(5,10) knownX = Array(10,20) x = 15 y = WorksheetFunction.Forecast(x,knownY,knownX) Or: y = WorksheetFunction.Forecast(x,Array(y1,y2),Array(x1 ,x2)) MsgBox y If you still cannot figure it, provide the relevant VBA context -- * declaration of variables and code fragment showing how those variables are set up. These options work for me. Many thanks to all. Rod |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Syntax question: using worksheet functions on a subset of a 3-D ar | Excel Programming | |||
General Question regarding passing cells, ranges, cell values through subs & functions | Excel Programming | |||
General Question regarding passing cells, ranges, cell values through subs & functions | Excel Programming | |||
General Question regarding passing cells, ranges, cell values through subs & functions | Excel Programming | |||
General Question regarding passing cells, ranges, cell values through subs & functions | Excel Programming |