Urgent help regarding line chart thru Excel-VBA

[kalyan]

Hi All

I'm a newbie to the Excel-VBA .I need a code for my requirement and the
requirement is

if have three arrays

one array---random dates in a year say 2006

second array---random numbers between 1 and 50

third array--random numbers between 5 and 100

All the array sizes are of same length that is all of the three have
same upper bounds

now i need to generate an excel line chart programmatically such that

it contains one X-axis and two Y-axis(one on left and one on right)

pictorially graph should look like below

| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
|---------------------------------------------------------|

one array should be plotted on X-axis
second array on left Y-axis
third array on right Y-axis

Please help...

Thanks & Regards
Kalyan Kamesh

 

[Tushar Mehta]

Transfer the data from the three arrays into three columns using syntax
similar to (the untested)

range("a1").resize(ubound(arr1)-lbound(arr1)+1,1).value= _
application.worksheetfunction.transpose(arr1)

To get the code for the rest, turn on the macro recorder (Tools | Macro >
Record new macro...), do the following by hand, turn off the recorder,
switch to the VBE and, if necessary, customize/generalize the XL-generated
code.
Click anywhere inside the the three columns then use the Chart Wizard to
create the chart. Once that's done, move one of the series to the secondary
axis. To do so, double-click the plotted series, then from the Axes tab
select 'Secondary'.

--
Regards,

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

 

[kalyan]

Hi Tushar

I want all this process to be done programmatically...hope you
understand...

Regards
Kalyan

 

[Tushar Mehta]

Hi Tushar

I want all this process to be done programmatically...hope you
understand...

Yes, and I told you how to get XL to generate the code for you!

--
Regards,

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

 

[kalyan]

Hi Tushar

I have worked out in the way which u have specified me earlier..but in
that process the excel is generating a chart as a sheet...but i do
require a chart that is embedded in the sheet..so i have modified the
code as below


With ActiveSheet.ChartObjects.Add _ (Left:=100, Width:=375, Top:=970,
Height:=225) With .Chart .ChartType = xlLineMarkers .SetSourceData
Source:=Sheets("Sheet3".Range("A1:C28", PlotBy _ :=xlColumns
..Parent.Name = "Name of this Chart" End With End With

but the problem is i'm not getting dual Y-Axis
can u help me...

Thanks & Regards
Kalyan Kamesh

 

[Jon Peltier]

You need to tell Excel to plot one series on the secondary axes:

ActiveChart.SeriesCollection(2).AxisGroup = xlSecondary

- Jon

 

[kalyan]

Thanks Jon that worked great

Thanks & Regards
Kalyan Kamesh

 

[Gklass]

kalyan wrote:

No problem and no code needed.



Select the second array -- choose "axis" and check
[]plot the array on the secondary axis

To begin, I assume you are doing a scatterplot, maybe with lines