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