Urgent help on plotting a X,Y graph...

[SPV]

Hi All,
I do some analysis on my excel sheet and I will get the range of
values need to be plotted on Excel..
So I have to plot a graph with columns Column_X, Column_Y1 and
Column_Y2 and Row range as Row_start and Row_end.

Al these above variables are declared as integeres.

Lets assume
Column_X =1(A), Column_Y1=2 (C) and Column_Y2=6(F)
Row_start= 10 , Row_end =25

If I specify the range in absolute way it will be,

Range("A10:A25,C10:C25,F10:F25").Select

Can anybody tell me how can I do the same in with row and column
numbers?

I have tried in two ways.

1. Using the Range approch
-------------------------------------------


Dim X_axis As Range
Dim Value_Y1 As Range
Dim Value_Y2 As Range


Set X_axis = ActiveSheet.Range(Cells(Row_start, Column_X ),
Cells(Row_end, Column_X ))
Set Value_Y1= ActiveSheet.Range(Cells(Row_start, Column_Y1),
Cells(Row_end, Column_Y1))
Set Value_Y2=ActiveSheet.Range(Cells(Row_start, Column_Y2),
Cells(Row_end, Column_Y2))


Range(X_axis, Value_Y1, Value_Y2).Select => didn't work for me..
Range("X_axis, Value_Y1, Value_Y2").Select => didn't work for me..

Is there any way to select different column range ?

2. Using Cells() approach
-------------------------------------

This also didn't work for me...
I was trying to emulate "Range("A10:A25,C10:C25,F10:F25").Select"
with

Range((Cells(Row_start, X_Axis):Cells(Row_end, X_Axis)) ,
(Cells(Row_start, Value_Y2):Cells(Row_end, Value_Y1)),
(Cells(Row_start,ValueY2):Cells(Row_end,ValueY2))).Select


I am a newbie in excel .. Your inputs will be appreciated....


Please find the macro that uses the absolute reference to plot the
graph. I have to change absolute reference with the calculated value
of row and column

Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range( _
"A2:A14,E2:E14,H2:H14"), PlotBy:=xlColumns

ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "ARM Load"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time
in Sec"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Load in
%"
End With


Again...
With out specifying Sheet name how can I add the chart object.. May be
aurrent activated sheeet ? What is the syntax?





Regards,
Shyju

 

[Mike Middleton]

Shyju -

I don't have the time to examine your code or to give you step by step
methods, but I do have example unprotected VBA code available in the Better
Histogram add-in that you can download from www.treeplan.com. The histogram
uses a column chart type with an XY (Scatter) chart type, and there are
separate VBA modules for Excel 97-2003 and Excel 2007.

- Mike
www.MikeMiddleton.com

 

[Jon Peltier]

Don't use select. It causes screen flashing and slows execution. You rarely
have to select what you are working on.

Dim X_axis As Range
Dim Value_Y1 As Range
Dim Value_Y2 As Range
Dim ChartRange As Range

With ActiveSheet
Set X_axis = .Range(.Cells(Row_start, Column_X), _
.Cells(Row_end, Column_X))
Set Value_Y1= .Range(.Cells(Row_start, Column_Y1), _
.Cells(Row_end, Column_Y1))
Set Value_Y2=.Range(.Cells(Row_start, Column_Y2), _
.Cells(Row_end, Column_Y2))

Set ChartRange = Union(X_axis, Value_Y1, Value_Y2)
End With

'....

ActiveChart.SetSourceData Source:=ChartRange, PlotBy:=xlColumns


- Jon