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

In article <[email protected]>,
[email protected] says...
> 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
>
>

[kalyan]

Hi Tushar

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

Regards
Kalyan


Tushar Mehta wrote:
> 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
>
> In article <[email protected]>,
> [email protected] says...
> > 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]

In article <[email protected]>,
[email protected] says...
> 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

Tushar Mehta wrote:
> In article <[email protected]>,
> [email protected] says...
> > 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

[Jon Peltier]

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

ActiveChart.SeriesCollection(2).AxisGroup = xlSecondary

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"kalyan" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> Tushar Mehta wrote:
>> In article <[email protected]>,
>> [email protected] says...
>> > 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]

Thanks Jon that worked great

Thanks & Regards
Kalyan Kamesh


Jon Peltier wrote:
> You need to tell Excel to plot one series on the secondary axes:
>
> ActiveChart.SeriesCollection(2).AxisGroup = xlSecondary
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
>
>
> "kalyan" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> > Tushar Mehta wrote:
> >> In article <[email protected]>,
> >> [email protected] says...
> >> > 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
> >

[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


> 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
>