Hi all,
I'm not really familiar with VBA and am struggling at the moment. What I'm aiming to be able to do is have a scatter plot that displays date labels for each point as well as distinguish the points by year by using different colors. I found a VBA code for scatter plot date labels and I also realize that (for me at least), the easiest way to distinguish the years by color would be to separate them with a logical statement and let the chart do the rest of the work by categorizing them as separate data ranges. However, I tried to do this with the VBA code that I found and it expectedly did not work. I'm not sure if it's an easy fix to just add more "xvalues" columns in the code or if what I want to do here is even possible.
Any help would be much appreciated!
Format and Code for the Date Labels:
<tbody>
</tbody>
Sub AttachLabelsToPoints()
'Dimension variables.
Dim Counter As Integer, ChartName As String, xVals As String
' Disable screen updating while the subroutine is run.
Application.ScreenUpdating = False
'Store the formula for the first series in "xVals".
xVals = ActiveChart.SeriesCollection(1).Formula
'Extract the range for the data from xVals.
xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, _
Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))
xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1)
Do While Left(xVals, 1) = ","
xVals = Mid(xVals, 2)
Loop
'Attach a label to each data point in the chart.
For Counter = 1 To Range(xVals).Cells.Count
ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
True
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
Next Counter
End Sub
Format to try to get different colored points based on year (just took a smaller section):
<tbody>
</tbody>
I'm not really familiar with VBA and am struggling at the moment. What I'm aiming to be able to do is have a scatter plot that displays date labels for each point as well as distinguish the points by year by using different colors. I found a VBA code for scatter plot date labels and I also realize that (for me at least), the easiest way to distinguish the years by color would be to separate them with a logical statement and let the chart do the rest of the work by categorizing them as separate data ranges. However, I tried to do this with the VBA code that I found and it expectedly did not work. I'm not sure if it's an easy fix to just add more "xvalues" columns in the code or if what I want to do here is even possible.
Any help would be much appreciated!
Format and Code for the Date Labels:
Date Time | Rainfall (mm) | 24 hr RDII Volume (L/ha/day) |
11/06/06 | 73.3 | 7,482.0 |
01/02/07 | 71.6 | 8,645.7 |
01/19/07 | 33.3 | 6,071.8 |
01/23/07 | 46.3 | 6083.7 |
02/15/07 | 42.4 | 3521.9 |
02/19/07 | 29.1 | 2856.2 |
03/11/07 | 86.1 | 13,397.6 |
10/31/12 | 43.5 | 6,813.8 |
11/03/12 | 32.8 | 6,337.0 |
11/20/12 | 26.5 | 4,174.3 |
12/01/12 | 32.5 | 5391 |
12/20/12 | 46.3 | 9774.4 |
<tbody>
</tbody>
Sub AttachLabelsToPoints()
'Dimension variables.
Dim Counter As Integer, ChartName As String, xVals As String
' Disable screen updating while the subroutine is run.
Application.ScreenUpdating = False
'Store the formula for the first series in "xVals".
xVals = ActiveChart.SeriesCollection(1).Formula
'Extract the range for the data from xVals.
xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, _
Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))
xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1)
Do While Left(xVals, 1) = ","
xVals = Mid(xVals, 2)
Loop
'Attach a label to each data point in the chart.
For Counter = 1 To Range(xVals).Cells.Count
ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
True
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
Next Counter
End Sub
Format to try to get different colored points based on year (just took a smaller section):
Date Time | 06/07 Rainfall | 12/13 Rainfall | 13/14 Rainfall | 24 hr RDII Volume (L/ha/day) | Rainfall (mm) | |
11/06/06 | 73.3 | #N/A | #N/A | 7,482.0 | 73.3 | |
01/02/07 | 71.6 | #N/A | #N/A | 8,645.7 | 71.6 | |
01/19/07 | 33.3 | #N/A | #N/A | 6,071.8 | 33.3 | |
01/23/07 | 46.3 | #N/A | #N/A | 6083.7 | 46.3 | |
02/15/07 | 42.4 | #N/A | #N/A | 3521.9 | 42.4 | |
02/19/07 | 29.1 | #N/A | #N/A | 2856.2 | 29.1 | |
03/11/07 | 86.1 | #N/A | #N/A | 13,397.6 | 86.1 | |
10/31/12 | #N/A | 43.5 | #N/A | 6,813.8 | 43.5 | |
11/03/12 | #N/A | 32.8 | #N/A | 6,337.0 | 32.8 | |
11/20/12 | #N/A | 26.5 | #N/A | 4,174.3 | 26.5 | |
12/01/12 | #N/A | 32.5 | #N/A | 5391 | 32.5 | |
12/20/12 | #N/A | 46.3 | #N/A | 9774.4 | 46.3 |
<tbody>
</tbody>