VBA for plotting chart, identifying the correct data column

R

roc_on_the_rocks

Board Regular
Joined
Jun 6, 2009
Messages
175
Office Version
  1. 365
Platform
  1. Windows
I deal with a lot of data logging, which may contain thousand of rows, and hundreds of columns. The log variable names are always on Row 8. Actual log data starts on Row 11 on.

I want to make a VBA code to make a simple X-Y scatter chart to plot "Temperature" on Y-axis. X-axis is always on Column A, Row 11 on.

The tricky part is that "Temperature" (name & data) comes on different Columns each time (although name & data are always on the same Column).

I would HIGHLY APPRECIATE if someone could please help.

Many thanks.

PS.: Excel 2007
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Hey,

Try the following code:

Code: 
Sub CreateScatterChart()

Application.ScreenUpdating = False

Dim LastRow As Long
Dim xTitle As Range
Dim xData As Range
Dim yColumn As Long
Dim yTitle As Range
Dim yData As Range
Dim GraphRange As Range

'Find last row with data
LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

'Set x-axis title and data
Set xTitle = Range("A8")
Set xData = Range("A11:A" & LastRow)


'Find Temperature, set y-axis title
With Rows(8)
    Set yTitle = .Find("Temperature", LookIn:=xlValues)
    yColumn = yTitle.Column
    End With

'set y-axis data
Set yData = Range(Cells(11, yColumn), Cells(LastRow, yColumn))

'set total graph range
Set GraphRange = Union(xTitle, xData, yTitle, yData)

'create chart
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SetSourceData Source:=GraphRange
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveChart.SetElement (msoElementLegendNone)
    ActiveChart.ChartTitle.Text = "Chart Title Here"
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    Selection.Caption = xTitle
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
    Selection.Caption = yTitle
    
Application.ScreenUpdating = True

End Sub

It seems to work on my dummy data set, if you have any issues/questions please let me know and I'll try to help!

Cheers,
alx7000
 
Upvote 0
alx7000, thank you so much! It works like a charm!

If this is not too much trouble, could you please help me adding a second data set ("Pressure"), to the secondary Y-axis this time?

Your method to plot is so clean and neat, but got me off guard because you didn't use "SeriesCollection(n)".

PS.: Also thank you for commenting your code, it helps me greatly.

Roc_On_The_Rocks
 
Upvote 0
Hey,

Thanks for the kind words, glad it works!
I haven't used "SeriesCollection(n)" before, will have to look into it.

I've updated the code for the second data set:

Code: 
Option Explicit

Sub CreateScatterChart2()

Application.ScreenUpdating = False

Dim LastRow As Long
Dim xTitle As Range
Dim xData As Range
Dim yColumn As Long
Dim yTitle As Range
Dim yData As Range
Dim y2Column As Long
Dim y2Title As Range
Dim y2Data As Range
Dim GraphRange As Range

'Find last row with data
LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

'Set x-axis title and data
Set xTitle = Range("A8")
Set xData = Range("A11:A" & LastRow)


'Find Temperature, set y-axis title
With Rows(8)
    Set yTitle = .Find("Temperature", LookIn:=xlValues)
    yColumn = yTitle.Column
    End With

'set y-axis data
Set yData = Range(Cells(11, yColumn), Cells(LastRow, yColumn))

'Find Pressure, set y2-axis title
With Rows(8)
    Set y2Title = .Find("Pressure", LookIn:=xlValues)
    y2Column = y2Title.Column
    End With

'set y2-axis data
Set y2Data = Range(Cells(11, y2Column), Cells(LastRow, y2Column))

'set total graph range
Set GraphRange = Union(xTitle, xData, yTitle, yData, y2Title, y2Data)

'create chart
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SetSourceData Source:=GraphRange
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    Selection.Caption = xTitle
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
    Selection.Caption = yTitle & " & " & y2Title
    
Application.ScreenUpdating = True

End Sub

Let me know how it goes!

Cheers,
alx7000
 
Upvote 0
Awesome, thanks again alx7000!

Both data sets are plotted on the primary Y-axis, but I can manage to move the second data set to the secondary Y-axis.

Cheers,
Roc_On_The_Rocks.
 
Upvote 0
Apologies for that, didn't read the request clearly!

Give the following code a go, any questions please let me know :)

Code: 
Option Explicit

Sub CreateScatterChart3()

Application.ScreenUpdating = False

Dim CurrentSheet As String
Dim LastRow As Long
Dim xTitle As Range
Dim xData As Range
Dim yColumn As Long
Dim yTitle As Range
Dim yData As Range
Dim y2Column As Long
Dim y2Title As Range
Dim y2Data As Range
Dim GraphRange As Range

'set current sheet
CurrentSheet = ActiveSheet.Name

'Find last row with data
LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

'Set x-axis title and data
Set xTitle = Range("A8")
Set xData = Range("A11:A" & LastRow)


'Find Temperature, set y-axis title
With Rows(8)
    Set yTitle = .Find("Temperature", LookIn:=xlValues)
    yColumn = yTitle.Column
    End With

'set y-axis data
Set yData = Range(Cells(11, yColumn), Cells(LastRow, yColumn))

'Find Pressure, set y2-axis title
With Rows(8)
    Set y2Title = .Find("Pressure", LookIn:=xlValues)
    y2Column = y2Title.Column
    End With

'set y2-axis data
Set y2Data = Range(Cells(11, y2Column), Cells(LastRow, y2Column))

'set total graph range
Set GraphRange = Union(xTitle, xData, yTitle, yData)
GraphRange.Select

'create chart
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SetSourceData Source:=GraphRange
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    Selection.Caption = xTitle
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
    Selection.Caption = yTitle

'add pressure series
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Name = y2Title
    ActiveChart.SeriesCollection(2).XValues = xData
    ActiveChart.SeriesCollection(2).Values = y2Data

'move pressure to secondary axis
    ActiveChart.SeriesCollection(2).AxisGroup = 2
    ActiveChart.SetElement (msoElementSecondaryValueAxisTitleRotated)
    Selection.Caption = y2Title
    
Application.ScreenUpdating = True

End Sub

Cheers,
alx7000
 
Upvote 0
Solution
It works great alx7000!

It also helped me big time in educating me to do charts through VBA.

Cheers,
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Neep help setting up table for plot graph
Replies
0
Views
89
supermom28115
S
O
  • Question
Creating a Graph/Chart needing TWO secondary axis options for a combination of Clustered and Stacked Graph Columns
Replies
0
Views
315
OrangeJuice
O
P
  • Question
How do I make these two pivot charts from this specific table? I can't figure it out, and this is a very common table format.
Replies
1
Views
223
awoohaw
awoohaw
B
  • Question
Prevent Line from Continuing Point-to-Point (Non-continuous) On Scatter Plot in VBA
Replies
1
Views
455
Jon Peltier
J
S
  • Question
Dynamic stacked bar chart, with variable x-axis and categorical variables
Replies
2
Views
1K
SYKEMAKAVELI
S

Forum statistics

Threads
1,214,819
Messages
6,121,749
Members
449,050
Latest member
excelknuckles

Share this page

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