Problem naming a range.

[Oufti]

Hello,

I'm sure you'll all laugh out loud with this one, I'm sorry but I'm really
incompetent in programming.

The background: I'm trying to do a VBA script independant from the file where
it will be run, here's a part of it:

CurrentSheet = ActiveSheet.name
With Range("F:G")
Range("F8:G" & .Find("*", .Item(1), , , , xlPrevious).Row).Select
ActiveWorkbook.Names.Add name:="plot", RefersTo:=Selection.Address
End With
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Sheets(CurrentSheet).Range("F8:G1889"), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, name:=CurrentSheet

The source for the chart will always begin in F8 but the column height may
vary, that's why in need to replace 'Range("F8:G1889")' by a non dependant
expression, I tried placing "plot" as a range but with no success...

Please be patient with me, It's the first time I work in this soft (I'm a
graphist) I'm sure there's a simple solution, I tried storing the range in
variables with things like:

Dim PLage As Range
Set PLage = Range("F8:G1889")

It works but I dont know the G1889 in advance I know how to find it by the
last non null cell method but cant understand how integrate it here.

Thanks in advace for your help and sorry for my piggin english.

 

[Don Guillett]

I would use a defined name
insert>name>define>name it plot>in the refers to box
=offset($f$8,0,0,counta($f:$f)+7,2)
Look in help index for offset to see. The +7 assumes true blanks in rows 1-7
then in yourseries source
=yourworkbookname.xls!plot

 

[Tushar Mehta]

Assuming Plot is properly defined, you can *try*:

Instead of

ActiveChart.SetSourceData Source:=Sheets(CurrentSheet).Range
("F8:G1889"), PlotBy:=xlColumns

use

ActiveChart.SetSourceData Source:=Sheets(CurrentSheet).Range("Plot"),
PlotBy:=xlColumns

Alternatively, check
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tom Ogilvy]

Sub AA()
Dim rng As Range
Dim currentSheet As String
currentSheet = ActiveSheet.Name
Set rng = Range(Cells(8, "F"), _
Cells(Rows.Count, "F").End(xlUp))
Set rng = rng.Resize(, 2)
ActiveWorkbook.Names.Add Name:="plot", _
RefersTo:="=" & _
rng.Address(External:=True)

Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=rng, PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, _
Name:=currentSheet
End Sub

I don't use PLOT in defining the chart, but left it in there just to show
how to define it if you want to try to use it.

 

[Oufti]

Tom Ogilvy a ecrit:

Sub AA()
...

Thanks a lot, it works perfectly !

I even think I understand what I just paste...

Thanks to all for your help, great NG.

Regards,