VBA Graph Logic

Lothrian

New Member
Joined
Jun 13, 2023
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am trying to build a tool which will make a graph with a variable no. of entries. I an using the "Range.CurrentRegion" method and if I have more than 3 data points it will plot the graph properly, but when i only have 1-3 data points the logic of excel switches the X axis data with the Legend Data.

Example of data ranges:
Working example
BF BG BH BI
Project No. FTQ LTQ Target
1 66 80 80
2 50 80 80
3 80 80 80
4 80 80 80
5 50 90 80

Failing example
BF BG BH BI
Project No. FTQ LTQ Target
1 66 80 80
2 50 80 80
What is happening is, ...
The
Purple text is the X Axis Data, the Red Text is the Legend Data and the Blue Text is the Y Axis data
From my tabular data example you can see the logic in excel using the "Range.CurrentRegion" plotting method has switched the Red & Purple data selections and therefore creates a graph which is not usable.


Can anyone suggest a method to plot the graphs using VBA code.

The current code I have is:
'IDR Graph
Dim IDRG As ChartObject
Set IDRG = FTQ.ChartObjects.Add(Left:=920, Top:=50, Width:=450, Height:=450)
With IDRG.Chart
IDRG.Chart.SetSourceData FTQ.Range("BF1").CurrentRegion
.ChartType = xlColumnClustered
.ApplyLayout 1
.Axes(xlCategory).Select
Selection.TickLabels.Orientation = xlDownward
.Axes(xlValue).Select
.Axes(xlValue).MaximumScale = 100
.ChartTitle.Text = "IDR FTQ & LTQ"
.FullSeriesCollection(3).ChartType = xlLine
.FullSeriesCollection(3).AxisGroup = 1
.FullSeriesCollection(3).Select
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
.Weight = 2.5
End With
End With


This is repeated multiple times with 5 data sets each has a preassigned "ChartObject" Variable and location information the present neatly. "FTQ is the Sheet name and appears in the VBA Tool as "FTQ(FTQ)"

Thank you for reading, I hope someone can help.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Thank you to those who looked into this. I believe I have found the issue.
 
Upvote 0

Forum statistics

Threads
1,222,398
Messages
6,165,770
Members
451,986
Latest member
samwize

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