how to select current range
 

Hi all,
While I'm comfortable with programming, I'm rusty and I have never see
VBA before.

I'm trying to make a macro that makes a graph with trendline based o
whatever range of numbers I have selected. I guess that means that i
the code below, I want to change the sheet and range references t
whatever is currently selected. Your answer will be appreciated.
Thanks.

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("R
INT").Range("AC12:AC15")
ActiveChart.Location Whe=xlLocationAsObject, Name:="R1 INT"
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Typ e:=xlLinear
Forward:=0, _
Backward:=0, DisplayEquation:=True
DisplayRSquared:=False).Select
ActiveChart.SeriesCollection(1).Trendlines(1).Data Label.Select
Selection.Left = 141
Selection.Top = 1
End Su

--
miker
-----------------------------------------------------------------------
mikerr's Profile: http://www.excelforum.com/member.php...fo&userid=2583
View this thread: http://www.excelforum.com/showthread.php?threadid=39219

how to select current range
 

Possibly:

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Selection
ActiveChart.Location Whe=xlLocationAsObject, Name:="R1 INT"
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Typ e:=xlLinear,
Forward:=0, _
Backward:=0, DisplayEquation:=True,
DisplayRSquared:=False).Select
ActiveChart.SeriesCollection(1).Trendlines(1).Data Label.Select
Selection.Left = 141
Selection.Top = 1
End Sub

--
Regards,
Tom Ogilvy

"mikerr" wrote in
message ...

Hi all,
While I'm comfortable with programming, I'm rusty and I have never seen
VBA before.

I'm trying to make a macro that makes a graph with trendline based on
whatever range of numbers I have selected. I guess that means that in
the code below, I want to change the sheet and range references to
whatever is currently selected. Your answer will be appreciated.
Thanks.

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("R1
INT").Range("AC12:AC15")
ActiveChart.Location Whe=xlLocationAsObject, Name:="R1 INT"
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Typ e:=xlLinear,
Forward:=0, _
Backward:=0, DisplayEquation:=True,
DisplayRSquared:=False).Select
ActiveChart.SeriesCollection(1).Trendlines(1).Data Label.Select
Selection.Left = 141
Selection.Top = 1
End Sub


--
mikerr
------------------------------------------------------------------------
mikerr's Profile:
http://www.excelforum.com/member.php...o&userid=25830
View this thread: http://www.excelforum.com/showthread...hreadid=392199

how to select current range
 

Thanks, Tom

Strangely, I'm getting an error of "run-time error 13, type-mismatch. "
The debugger then throws me back to the line:

ActiveChart.SetSourceData Source:=Selection

This seems to be happening regardless of what sheet or cell range I am
on. As far as I can tell, this is the only code below that you
recommended changed.

Again, I do appreciate your help (or anyone else's) as this would save
me quite a few keystrokes.


--
mikerr
------------------------------------------------------------------------
mikerr's Profile: http://www.excelforum.com/member.php...o&userid=25830
View this thread: http://www.excelforum.com/showthread...hreadid=392199

how to select current range
 

Then try:

ActiveChart.SetSourceData Source:=Selection.Address(1,1,xlR1C1,True)


--
Regards,
Tom Ogilvy

"mikerr" wrote in
message ...

Thanks, Tom

Strangely, I'm getting an error of "run-time error 13, type-mismatch. "
The debugger then throws me back to the line:

ActiveChart.SetSourceData Source:=Selection

This seems to be happening regardless of what sheet or cell range I am
on. As far as I can tell, this is the only code below that you
recommended changed.

Again, I do appreciate your help (or anyone else's) as this would save
me quite a few keystrokes.


--
mikerr
------------------------------------------------------------------------
mikerr's Profile:
http://www.excelforum.com/member.php...o&userid=25830
View this thread: http://www.excelforum.com/showthread...hreadid=392199

how to select current range
 

Well, as it turns out, I managed to find a different way:

Charts.Add
ActiveChart.Location Whe=xlLocationAsObject, Name:="R1 INT"
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Typ e:=xlLinear
Forward:=0, _
Backward:=0, DisplayEquation:=True
DisplayRSquared:=False).Select
ActiveChart.SeriesCollection(1).Trendlines(1).Data Label.Select
Selection.Left = 142
Selection.Top = 1
End Sub

I'm not quite sure how it works, but it does. Peltiertech.co
recommended changing the order of 2 of the lines, and that did th
trick.

Only 1 problem left: It puts these graphs on the "R1 Int" Sheet, whe
I want it on whatever is my active sheet. I tried:

ActiveChart.Location Whe=xlLocationAsObject, Name:=ActiveSheet

but it throws me back a "run-time error 5". Any ideas? Thanks

--
miker
-----------------------------------------------------------------------
mikerr's Profile: http://www.excelforum.com/member.php...fo&userid=2583
View this thread: http://www.excelforum.com/showthread.php?threadid=39219