Macro for multiple charting of multiple datasets

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()

' create and format all charts macro - for one TC test
'
' Macro created 1/11/2006 by mmf

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

Sorry - wasn't aware of the usefulness of code tags!

Please Login or Register  to view this content.