Column/Scatter chart composite macro problems

llocklee

New Member
Joined
Sep 13, 2011
Messages
2
I'm very new to doing charts with macros. I'm trying to replicate the Peltier chart with coloured quadrant (using stacked column) and then overlayed with a scatter chart

http://peltiertech.com/Excel/Charts/ColoredQuadrantBackground.html

I initially recorded a macro while doing this but based on what I have seen on the forums the code is next to useless....giving many runtime errors.

I have managed to get the first part, the colored quadrants going but am running into problems once I try to overlay the scatter chart. I've tried many methods but I can't find a way to address the current chart and then change the chart type to scatter without wiping out the column graph or getting various runtime errors.

Here is the code fragment I'm struggling with .. there are probably many problems :(

Set chtObjs = CSD.ChartObjects
Set myChtObjs = CSD.ChartObjects ' DON"T KNOW WHAT TO DO HERE?
With myChtObjs.Chart ' GIVES ERRORS HERE

' define chart data range for the row (record)

Set rngChtData = WSD.Range("C2" & ":C" & no_vertices + 1)
Set rngChtXVal = WSD.Range("B2" & ":B" & no_vertices + 1)
.ChartType = xlXYScatter

.SeriesCollection.NewSeries
With ActiveChart.SeriesCollection.NewSeries
.Values = rngChtData
.XValues = rngChtXVal
.HasTitle = True
.ChartTitle.Characters.Text = "Demand Matrix"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "No. of Nominations"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "% Connections External to Group"
.Axes(xlCategory).HasMajorGridlines = False
.Axes(xlCategory).HasMinorGridlines = False
.Axes(xlValue).HasMajorGridlines = False
.Axes(xlValue).HasMinorGridlines = False
.Axes(xlValue).MaximumScale = 100
.HasLegend = False
.Parent.name = "Demand Matrix"
.name = "Demand Matrix"

.MarkerStyle = 5
.MarkerSize = 7
End With
End With
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
In the next week or so, I'm writing a blog post showing how to construct a macro to draw a chart, and I'm using this chart type as an example. I was starting it even before I saw your post. I just need to get a little time to finish it.
 
Upvote 0

Forum statistics

Threads
1,224,525
Messages
6,179,319
Members
452,905
Latest member
deadwings

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top