help on macro for making chart


R

roger

Hi,
i want to make a plot using macro where I want my x-axis to be the
value from the same column but random rows for example

R18C3,R19C3,R21C3,R23C3,R25C3,R27C3 ( only rows are changing)

and my Y-axis should also be something like this

R18C5,R19C5,R21C5,R23C5,R25C5,R27C5 ( only rows are changing)

For one plot It is working but i do not know how i can plot for other
columns...

the code looks like this

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 3/30/2007 by Roger
'
' Keyboard Shortcut: Ctrl+m
'
Charts.Add
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = _
"=('UV and UV & Ozone'!R18C3,'UV and UV & Ozone'!R19C3,'UV and
UV & Ozone'!R21C3,'UV and UV & Ozone'!R23C3,'UV and UV & Ozone'!
R25C3,'UV and UV & Ozone'!R27C3)"
ActiveChart.SeriesCollection(1).Values = _
"=('UV and UV & Ozone'!R18C37,'UV and UV & Ozone'!R19C37,'UV
and UV & Ozone'!R21C37,'UV and UV & Ozone'!R23C37,'UV and UV & Ozone'!
R25C37,'UV and UV & Ozone'!R27C37)"
ActiveChart.SeriesCollection(1).Name = "=""UV"""
ActiveChart.SeriesCollection(2).XValues = _
"=('UV and UV & Ozone'!R18C3,'UV and UV & Ozone'!R19C3,'UV and
UV & Ozone'!R21C3,'UV and UV & Ozone'!R23C3,'UV and UV & Ozone'!
R25C3,'UV and UV & Ozone'!R27C3)"
ActiveChart.SeriesCollection(2).Values = _
"=('UV and UV & Ozone'!R18C37,'UV and UV & Ozone'!R20C37,'UV
and UV & Ozone'!R22C37,'UV and UV & Ozone'!R24C37,'UV and UV & Ozone'!
R26C37,'UV and UV & Ozone'!R28C37)"
ActiveChart.SeriesCollection(2).Name = "=""UV & Ozone"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="UV and UV &
Ozone"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "plot"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"time"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "MPN"
End With
End Sub
 
Ad

Advertisements

G

Guest

Roger,

You might be able to tailor this to your data:

Sub NonContinuousRangeChart()

Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range

Set Rng1 = Range("A2:A4:A6:A8")
Set Rng2 = Range("C2:C4:C6:C8")
Set Rng3 = Range("E2:E4:E6:E8")

Charts.Add
ActiveChart.ChartType = xlColumnClustered

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Values = Rng1

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = Rng2

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Values = Rng3

End Sub
 
J

Jon Peltier

These ranges

Set Rng1 = Range("A2:A4:A6:A8")
Set Rng2 = Range("C2:C4:C6:C8")
Set Rng3 = Range("E2:E4:E6:E8")

are not ranges consisting of multiple discontiguous cells, but are the
equivalent of the contiguous ranges

Set Rng1 = Range("A2:A8")
Set Rng2 = Range("C2:C8")
Set Rng3 = Range("E2:E8")

The OP can either edit his original code

ActiveChart.SeriesCollection(1).Values = _
"=('UV and UV & Ozone'!R18C37,'UV and UV & Ozone'!R19C37,'UV
and UV & Ozone'!R21C37,'UV and UV & Ozone'!R23C37,'UV and UV & Ozone'!
R25C37,'UV and UV & Ozone'!R27C37)"

to include the new cells, or incorporate it into John's suggestion:

Set Rng1 = Union(Worksheets("UV and UV & Ozone").Range("AK18"),
Worksheets("UV and UV & Ozone").Range("AK19"), Worksheets("UV and UV &
Ozone").Range("AK21"), Worksheets("UV and UV & Ozone").Range("AK23"),
Worksheets("UV and UV & Ozone").Range("AK25"), Worksheets("UV and UV &
Ozone").Range("AK27"))

ActiveChart.SeriesCollection(1).Values = Rng1

- Jon
 
Ad

Advertisements

R

roger

Thanks a lot but couldn't figure it out how to do it...as i am not
computer savvy...I would appreciate if you can explain it in more
detail.

Just for the information....what I need is...when I will press the
Macro shortkey say (crlt + m) the program should make plots with
different columns keeping the rows same as before.

Thank you
roger
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top