mmf144
01-12-2006, 08:21 AM
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
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