Macro for multiple charting of multiple datasets

[mmf144]

Hello,

I've been working on this problem now (intermittently) for about a month, and I've Googled to my fullest extent, but I've hit the wall and I need help from a more experienced VBA user.

What I am trying to do is write a macro that will automatically grab multiple data sets, then chart them, format the charts, name the charts, etc. My spreadsheet is set up like this: in the A:A column, I have my x-values. More specifically, this column contains two sets of x-values (time starting at zero), one of which corresponds to an experimental set of temperature data, the other corresponding to a modeled set of temperature data. So this column is discontinuous at one point, where the first x-dataset ends and the second one begins again (at time = zero). The y-data are then listed in all subsequent columns, and are located adjacent to one another...so y-data-set1 lies in columns B:C, y-data-set2 in columns D:E, and so on. If you were to see the data (which I probably shouldn't share on the web!), you would see columns A:B containing data, but the C column data wouldn't show up until hundreds of rows down, when the second time range begins.

In the end, each chart should have two temperature-time curves, one of which corresponds to (for example) the data range (A2:A500, B2:B500), and the other, (A501:A700, C501:C700). As long as this data is correctly ?grabbed,? the formatting part of my code is fine, but something's apparently wrong with my grabbing method. I devised a For-Next procedure to cycle through the data columns based on the variable "col," (see the code below), and I am using a simple If-Then argument to ensure that the macro will stop trying to grab data when it comes to an empty column. However, although the macro seems to loop properly, and the chart formatting/naming is working, my problem is the following:

After the first chart is plotted, the data selection corresponding to that chart remains selected, so that all subsequent charts plot a cumulative data set (i.e. chart1 plots data from A:C, chart2 plots data from A:E, chart3 plots data from A:G, etc.), which I do not want. I can?t find a way to ?deselect? the data after charting it, but I don?t believe I should have to do so, anyway. I had some other problems regarding sourcedata specification and x-values showing up incorrectly, but they seemed to vanish overnight(!). So here is the code I have right now, which works perfectly except for the fact that it?s plotting cumulative charts of data:


Sub all_charts_create_and_format()

Dim col As Integer
For col = 2 To 100 Step 2
Sheets("Model vs. Experimental").Activate
If IsEmpty(Cells(2, col)) = False Then
Sheets("Model vs. Experimental").Range("A1:A5000", _
Range(Sheets("Model vs. Experimental").Columns(col), _
Sheets("Model vs. Experimental").Columns(col + 1))).Select
'this is the data selecting method
Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SetSourceData Source:=Sheets("Model vs. Experimental").Range("A1:A5000", _
Range(Sheets("Model vs. Experimental").Columns(col), _
Sheets("Model vs. Experimental").Columns(col + 1)))
'I had to use this seemingly redundant sourcedata method because originally,
'the data was not being plotted correctly
ActiveChart.Location Where:=xlLocationAsObject, Name:="Model vs. Experimental"
'(I have excluded all of the chart formatting code, but this is where it lies
'in the actual code)
End If
Next col
End Sub



I would greatly appreciate any help in this matter; I feel like my data selecting method must contain an error that I am not familiar enough with VBA to understand. And please, try to keep it simple!

Thanks for reading,

Matt

[Philcjr]

Matt,

Any chance of uploading the file?

Phil

[mdmackillop]

Hi Matt,
Welcome to VBAX.
I've edited your code to add line breaks and used the VBA tags which format it as shown to improve readability.
Regards
MD

[mmf144]

Phil,

Unfortunately, as tempting as it is, I cannot share this data. It's somewhat proprietary. However, I'll upload a workbook containing the macro and add a bunch of dummy data. There will be 4 total data sets to plot, but in real life, that number could be as high as 30, which is why I'm trying to get this code right.

I've tried numerous things throughout the day to try and "deselect" the data range after the first chart is created, but to no avail. At the end of the loop, you'll see where I tried to get out of the chart, activate the worksheet, and copy/paste something in a couple of empty cells. No good! I'm at the end of my wits!


Matt

[mdmackillop]

Hi Matt,
Give this a try. I disabled the axis scale in the attachment as it didn't really suit the data.

Sub MakeCharts()
Dim ChArea As Range, Rw As Long, i As Long
Application.ScreenUpdating = False
Rw = Cells(Cells.Rows.Count, "A").End(xlUp).Row()
For i = 2 To 6 Step 2
Sheets("Model vs. Experimental").Activate
Set ChArea = Union(Range(Cells(1, 1), Cells(Rw, 1)), _
Range(Cells(1, i), Cells(Rw, i + 1)))
Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SetSourceData Source:=ChArea, PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
DoFormat i
Next
Application.ScreenUpdating = True
End Sub

[mmf144]

MD,

Thanks so much; I am elated to see this work!! However, I have a couple of questions, because just having the macro work isn't enough for me - I want to understand it!

1) What does Application.ScreenUpdating = False do, and why does it revert to True after the For-Next loop closes?

2) I suspected the Union method might be the way to go, but I'm still not sure I understand why it works. Is it analagous to, in the worksheet, selecting column A, then holding CTRL, and selecting columns D:E, etc.? When I left work today I knew that my problem was the way I specified the sourcedata, i.e., using the Range method was selecting the "cumulative" range of data. The Union method must be the reason this all works now!

One more question:

For the upper scale limit, I'd like to go to the end of the first set of x-data, as you saw in my code, but instead of using that number, I want to round it up to the next hundred. So if it's 1415, I'd like the x-axis scale to end at 1500. Is this possible?

Thanks again profusely for getting me over the hurdle.

Matt

[mdmackillop]

1) What does Application.ScreenUpdating = False do, and why does it revert to True after the For-Next loop closes?

It stops the screen flashing as the code proceeds, try commenting out the first line.



2) I suspected the Union method might be the way to go, but I'm still not sure I understand why it works. Is it analagous to, in the worksheet, selecting column A, then holding CTRL, and selecting columns D:E, etc.?

Correct


So if it's 1415, I'd like the x-axis scale to end at 1500. Is this possible?

=(INT(1415/100)+1)*100

[mmf144]

Excellent. Thanks again. I learned a lot from this seemingly small amount of effort on your part! :)

M.

[mdmackillop]

Glad to be of help, especially where it's educational!:friends: