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