| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
help on macro for making chart
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 Whe=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 |
|
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
help on macro for making chart
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 -- John "roger" wrote: 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 Whe=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 |
|
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
help on macro for making chart
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "John Mansfield" wrote in message ... 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 -- John "roger" wrote: 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 Whe=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 |
|
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
help on macro for making chart
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| making chart using macro | Excel Discussion (Misc queries) | |||
| making chart using macro | Excel Discussion (Misc queries) | |||
| making a chart | Excel Discussion (Misc queries) | |||
| Chart making | Charts and Charting in Excel | |||
| Making a chart | Charts and Charting in Excel | |||
Linear Mode
