Results 1 to 3 of 3

Thread: DisplayBlanksAs = xlInterpolated for One Series Only

  1. #1

    DisplayBlanksAs = xlInterpolated for One Series Only

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Excel Version
    I suspect that two charts is going to be less kludgy than any other solution, to be honest.

  3. #3
    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((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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts