0

I've got a large set of data that I'm trying to go through to dynamically make a chart out of. Each column value is aligned with a specific time value. There is a column called "Type" that hold either an empty cell, "Log ", "Log+", "SRst", "ARst", or "WRst" that I'm looking to plot onto the chart along with other user-defined columns in a specified time range.

So far I can get the chart to plot properly, but no matter the X time value, it always shows up at the first index time value, then the second, then third and so on up until the n'th appearance of that "Type" value.

Here is what I have right now and I'm not sure why it isn't aligning them to the appropriate values:

' This entire section is in a With block for the chart in another sub with the sub check in it's own sub
' I just felt like it's extra unnecessary stuff to add
Dim c4X As Range
Dim c5X As Range
Dim c6X As Range
Dim c7X As Range
Dim c8X As Range

' yFollow is the column that the events (c4-8X) are plotted on the Y axis with
' sh is the worksheet
For Each c In sh.Range(evChar & sIndex & ":" & evChar & eIndex)
    If c.Value = "Log " Then
        Call check(c.Row, yFollow, sh, c4X)
    ElseIf c.Value = "Log+" Then
        Call check(c.Row, yFollow, sh, c5X)
    ElseIf c.Value = "SRst" Then
        Call check(c.Row, yFollow, sh, c6X)
    ElseIf c.Value = "ARst" Then
        Call check(c.Row, yFollow, sh, c7X)
    ElseIf c.Value = "WRst" Then
        Call check(c.Row, yFollow, sh, c8X)
    End If
Next c

' I'm aware this is a poor way, just make a function. I will change it after I figure this out
If Not c4X Is Nothing Then
    .SeriesCollection(i).XValues = c4X
End If
If Not c5X Is Nothing Then
    .SeriesCollection(i + 1).XValues = c5X
End If
If Not c6X Is Nothing Then
    .SeriesCollection(i + 2).XValues = c6X
End If
If Not c7X Is Nothing Then
    .SeriesCollection(i + 3).XValues = c7X
End If
If Not c8X Is Nothing Then
    .SeriesCollection(i + 4).XValues = c8X
End If

Sub check(ByVal c As Long, ByVal col As String, ByVal sh As Worksheet, ByRef xRng As Range)
    If xRng Is Nothing Then
        Set xRng = sh.Range("A" & c)
    Else
        Set xRng = Union(xRng, sh.Range("A" & c))
    End If
End Sub

You can see in excel the values are the starting time for some reason here. When hovering a point, here you can see the actual time value it should be. The graph as a whole looks like this. The ARst point on the graph is starting at 12:40:00. When hovering, it shows the value is 12:40:21. When right clicking the graph and selecting data, it shows the values to be 12:40:00-12:40:02.

I'm really unsure why when I'm setting the values that it's correct on point hovers, but not when it's actually plotted.

jakewags01
  • 41
  • 4
  • Maybe the problem is not clear to me, but: are you perhaps missing a column/row to labels the data? – Evil Blue Monkey Jul 07 '22 at 15:09
  • @EvilBlueMonkey The problem is that the points should be plotted more into the center of the graph. The points aren't aligned on the appropriate time value. I shouldn't be missing any other column/rows. – jakewags01 Jul 07 '22 at 15:15
  • Are the data "shifted" by a single position? If so, you might be missing a column/row; not in the sense that you are not selecting it: you need to create it. It has to label the data. The data you are missing and that won't be plotted are used as title in your horizontal axis label. – Evil Blue Monkey Jul 07 '22 at 15:20
  • @EvilBlueMonkey The data is just always being placed at the exact start of the points on the plot. Testing different data with points in the time frame anywhere, it always places them at the start of the chart so there's no x-displacement that's a common denominator kind of thing. – jakewags01 Jul 07 '22 at 15:38

0 Answers0