Syntax in worksheet functions for individual values instead of ranges

  • Thread starter Thread starter REM
  • Start date Start date
R

REM

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
 
REM said:
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)"
Click to expand...

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.
dont know how to evaluate this to a variable,
without using a cell.
Click to expand...

y = Evaluate("=TREND({5;10},{10;20},15)")

or [1]:

y = WorksheetFunction.Forecast(15, Array(5,10), Array(10,20))

Ideally I would like the numbers to be variables also.
Click to expand...

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))
 
I said:
[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))
Click to expand...

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).
 
PS.... I said:
[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))
Click to expand...

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.
Click to expand...

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
 
PS2.... I wrote:
joeu2004 said:
knownY = Array(5,10)
knownX = Array(10,20)
x = 15
y = WorksheetFunction.Forecast(x,knownY,knownX)
Click to expand...

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.
 
PS2....  I wrote:




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.
Click to expand...

These options work for me.
Many thanks to all.
Rod
 
Back
Top