DisplayBlanksAs = xlInterpolated for One Series Only

JonMulder

New member
Joined
Sep 18, 2011
Messages
2
Reaction score
0
Points
0
Greetings,
I'm using VBA to create over 200 hydrographs of water levels in monitoring wells. One seriescollection is continuous data (1 hour intervals by datalogger) and the other series is a manual measurement (about 4 times a year). I want the manual measurements to be interpolated so that a line connects the gaps between them, But I don't want the continuous data to interpolate gaps (i.e., when the data logger stopped recording).

It seems the "DisplayBlanksAs " is a property of the chart, but cannot vary between series. Any idea how to do this? I've started thinking of overlaying two chart objects on the same worksheet, but that seems kind of kludgy.
Any help would be appreciated!

Jon Mulder
California Department of Water Resources
Durham, CA USA
 
I suspect that two charts is going to be less kludgy than any other solution, to be honest.
 
Actually, I got the problem solved. Someone suggested creating a trendline for the Periodic Measurements. The code below worked for me. "k" is the number of wells that I plot on the same chart. I have to check that there actually is Periodic Data before creating a trendline. I do that by checking if the Max value is not equal to 0.


For i = 1 To k
.SeriesCollection.NewSeries
.SeriesCollection((k + 1) + i).XValues = Range("Date" & strSWN(i))
.SeriesCollection((k + 1) + i).Values = Range("PeriodicMeasurements" + strSWN(i))
.SeriesCollection((k + 1) + i).MarkerStyle = xlDiamond
.SeriesCollection((k + 1) + i).MarkerSize = 3
.SeriesCollection((k + 1) + i).Border.LineStyle = xlLineStyleNone
.SeriesCollection((k + 1) + i).MarkerForegroundColor = myCol(i)
.SeriesCollection((k + 1) + i).MarkerBackgroundColor = myCol(i)
.SeriesCollection((k + 1) + i).Name = "Periodic Measurements"
'Add Trendline with 2-point moving average to produce lines between Periodic Measurements.
'If there are no Periodic Measurements for this series, do not create a Trensline.
If Range("Max" & strSWN(i)) <> 0 Then
.SeriesCollection((k + 1) + i).Trendlines.Add
.SeriesCollection((k + 1) + i).Trendlines(1).Select
With Selection
.Type = xlMovingAvg
.Period = 2
'Do not show trendline in Legend.
.Name = "Periodic Measurement Trendline"
.Format.Line.ForeColor.RGB = myCol(i)
.Format.Line.Weight = 0.25
.Format.Line.DashStyle = msoLineLongDashDotDot
.Format.Line.Transparency = 0.5
End With
End If
Next i
 
Back
Top