Cut and Paste macro in Excel not changing trendline values

[willsteele]

I am trying to figure out a way around a bug in Excel. I have a table with values that are changing regularly and a graph based on those table values. I set up a macro to cut and paste the values of the trendline function in the graph, modify it and work it into other functions. The only problem is that when the macro runs, it reposts the same function every time. I'll post the code below:

Sub calculateVO2()
'
' calculateVO2 Macro
' Macro recorded 9/15/2005 by Bill Steele
'
' Keyboard Shortcut: Ctrl+Shift+Z
'
    ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
    ActiveWindow.Visible = False
    Windows("tinman-20 mile calculator.xls").Activate
    ActiveCell.Select
   ActiveCell.FormulaR1C1 = _
        [I]"= 0.000822294662032708*RC[1]^6 - 0.266599848942608*RC[1]^5 + 35.8848498068709*RC[1]^4 - 2566.84446514638*RC[1]^3 + 102912.819753766*RC[1]^2 - 2192977.28925606*RC[1]^1 + 19406383.8609026"[/I]
    ActiveCell.Select
    ActiveCell.GoalSeek Goal:=420, ChangingCell:=ActiveCell.Offset(0, 1).Range( _
        "A1")
End Sub

Above the italicized part is what I am trying to make variable with each macro execution. Is there a way this can be done? For whatever reason, it either gives me an error or reposts the same data.

Thanks,

Will

[RhinoBull]

Welcome to Forums, will!

You may have a btter chance to get answer if you post in the VBA Forum .

[RobDog888]

Moved from Classic VB forum.

[willsteele]

Thanks guys. I'm just trying to figure out VB this week. Sorry if this question is something obvious I'm just not catching. I look forward to learning as much as I can.

Will

[RobDog888]

What error are you getting?

You can use variables to hold data in place of the hard coded values., but they need to come from somewhere or be set somehow.

ActiveCell.FormulaR1C1 = _
        "= 0.000822294662032708*RC[1]^6 - 0.266599848942608*RC[1]^5 + 35.8848498068709*RC[1]^4 - 2566.84446514638*RC[1]^3 + 102912.819753766*RC[1]^2 - 2192977.28925606*RC[1]^1 + 19406383.8609026"
 
'Would look something like this to be dynamic
ActiveCell.FormulaR1C1 = _
        "= " &  MyVar6 & " *RC[1]^6 - " &  MyVar5 & " *RC[1]^5 + " &  MyVar4 & " *RC[1]^4 - " &  MyVar3 & " *RC[1]^3 + " &  MyVar2 & " *RC[1]^2 - " &  MyVar1 & " *RC[1]^1 + " & MyVar0

Is this how you meant?

[willsteele]

The error I am getting is as follows:

Run-time error '1004';

Unable to get the ChartObjects property of the Worksheet class

The idea of assigning variables is great, I'm just not fluent enough to know how to assign the variables, fit them into the script and have them work correctly every time. I could try my basic programming skills to hack at it, but don't know how far I can get. Basically, I started record Macro, ran through my steps, then tried to run it. Didn't work. I see how assigning variables would do the job, I just don't know how to write out that code. (I just got some VB for Dummies and the O'Reilly "In a Nutshell" VBA Series from the library a few days ago.)

Thanks for the help!

Will

[sanjaylimbikai]

The code recommended by DOG- MyVar6, MyVar5.. etc, are actually constants of the trend formula for the given data, if you change the data the constants are changing. As in your original post you are saying the data is chaning regularly, then you must extract the constants and then use them in your macro, you can extract these constants without using trend chart.

Sanjay

[willsteele]

So, I would declare the variables, leave the code as below in the same place, then add in the variables somewhere else? Or, would declaring the variables at the beginning of the macro be enough?

[RobDog888]

No, you also need to set them to their values. What are they dependant upon? A certain sheets cell value or something else? If a cell then set the variables equal to those cells. Then when the cell changes value the variable will and so shuld the chart. But in that situation a cell reference would be able to replace the variable since ts a step saver.

[willsteele]

Is there a way to find trendline coefficient values without doing the chart? Perhaps a matrix? I don't know excel well enough to know the internals of the function to bypass the charting step. If I could just have this, the macro would be unnecessary.

[RobDog888]

I havent really done a whole lot with charting but I remember from my high school that there is a formule for calculating any graph. So I guess it dependant upon what your charting and the formulas your using.

[willsteele]

It's a 6th order polynomial based on 17 paired cells of data.

[sanjaylimbikai]

you can easily calculate the coefficients of the trend using linest function, here it is how -

assume your data is from A1 to B11 (10 values) for your trend. Now somewhere select 5 cells in a row (e.g. F4-J4) and enter for formula as =LINEST(A2:A11, B2:B11^{1,2,3,4}), and press ctrl+shift+enter, thats all, now these are actually the constant of the trend, verify them with drawing a trend chart of 4th order.

[willsteele]

What is the highest degree this variable will calculate to? Thanks. I will try this out and see if it gets me where I need to go.

[sanjaylimbikai]

you can calculate coefficients for any order.

2nd Order Polynomial Trendline
Equation: y = (c2 * x^2) + (c1 * x ^1) + b

c2: =INDEX(LINEST(y,x^{1,2}),1)

C1: =INDEX(LINEST(y,x^{1,2}),1,2)

b = =INDEX(LINEST(y,x^{1,2}),1,3)

3rd Order Polynomial Trendline
Equation: y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b

c3: =INDEX(LINEST(y,x^{1,2,3}),1)

c2: =INDEX(LINEST(y,x^{1,2,3}),1,2)

C1: =INDEX(LINEST(y,x^{1,2,3}),1,3)

b: =INDEX(LINEST(y,x^{1,2,3}),1,4)

Higher Order Polynomial Trendline
Notice the pattern in the two preceding sets of formulas

Sanjay

[willsteele]

I tried the index(linest approach and it didn't work out the way I hoped. Oh well, that would have definitely solved many of my problems. I guess I'll have to figure out how to make the variables work.

I'm trying to figure out how to insert the variable code from above:

ActiveCell.FormulaR1C1 = _
        "= 0.000822294662032708*RC[1]^6 - 0.266599848942608*RC[1]^5 + 35.8848498068709*RC[1]^4 - 2566.84446514638*RC[1]^3 + 102912.819753766*RC[1]^2 - 2192977.28925606*RC[1]^1 + 19406383.8609026"
 
'Would look something like this to be dynamic
ActiveCell.FormulaR1C1 = _
        "= " &  MyVar6 & " *RC[1]^6 - " &  MyVar5 & " *RC[1]^5 + " &  MyVar4 & " *RC[1]^4 - " &  MyVar3 & " *RC[1]^3 + " &  MyVar2 & " *RC[1]^2 - " &  MyVar1 & " *RC[1]^1 + " & MyVar0

by setting the variables. Do I declare those at the beginning of the code? If so, how do I set them? Sorry this is basic, I'm just not sure how VB works yet.

So far I've done this:

Sub VO2Max()
'
' VO2Max Macro
' Macro recorded 1/2/2000 by Bill Steele
'
' Keyboard Shortcut: Ctrl+Shift+M
'
int 6 ()=index(linest(C17:C37,D17:D37^{1,2,3,4,5,6},1))
;

But it gives me a compiler error. I know I'm not even close to getting it right, but am trying to get the idea here.

Thanks,

Will