Report

Copy ActiveChart properties to cell (from graphics) [Solved]

Ask a question mendokzeni - Last answered on Feb 2, 2017 at 01:57 PM by mendokzeni
Hello,

I hope someone could help me. Working with graphics in excel, I generate dispersion plots from some datas, later I add the trendline, display the equation and Rsquared; what I want is copy the equation and the Rsquared and paste it in some cell. I hope someone would help me.

I put the code I use:

Dim lastRow1 As Long
Dim lastRow2 As Long
'COPY DATA

lastRow1 = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
lastRow2 = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
Range("D11:D" & lastRow1 & ",E11:E" & lastRow2).Select

'Range(Range(Range("D11"), Range("D11").End(xlDown)), Range(Range("E11"), Range("E11").End(xlDown))).Select
'ADD PLOT

ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
ActiveChart.SetSourceData Source:=Range("D11:D" & lastRow1 & ",E11:E" & lastRow2)
ActiveChart.FullSeriesCollection(1).Select
ActiveChart.FullSeriesCollection(1).Trendlines.Add
ActiveChart.FullSeriesCollection(1).Trendlines(1).Select
'MOVE THE CHART
With ActiveChart.Parent
.Top = Range("N7").Top
.Left = Range("N7").Left
End With
'ADD TRENDLINE, EQUATION AND RSQUARED
Selection.Type = xlPower
Selection.DisplayEquation = True
Selection.DisplayRSquared = True

Thanks
See more 
Helpful
+0
plus moins
Hi Mendokzeni,

When you put the below piece of code at the end of your code, then the Equation value will be put in H1 and the RSquared will be put in H2. Change those cell references to put the values in a place to your liking.

Here is the piece of code:
Dim myER As String
Dim sNumb As Integer
ActiveChart.FullSeriesCollection(1).Trendlines(1).Select
myER = Selection.DataLabel.Text
sNumb = InStr(myER, "")

Range("H1").Value = Left(myER, sNumb - 1)
Range("H2").Value = Right(myER, Len(myER) - sNumb)


Best regards,
Trowa
Leave a comment
Helpful
+0
plus moins
Thank you very much TrowaD. With a few modifications it works perfectly.
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!