Can’t get chosen range on x axis.

[theherbalist]

Hi there, I’m really new to this whole vba thing so please be kind .

I’m trying to write a macro that selects a specific sheet, and then selects ranges on that sheet based on column title. With this selection I want it to draw a scatter graph. However, the problem I’m having is that once the graph is drawn the values on the x axis are only integers, not "time" which is the range of my choosing. Time is plotted as another series on the Y axis? How do I solve this? It’s also worth mentioning that this macro works on some files but not others, even though the files are identical in every way (e.g number of columns, column titles etc) except for the values within the cells and the number of cells with data.

here is the code:

Sub Plot()

Application.ScreenUpdating = False
   
Dim ws As Worksheet
    On Error Resume Next
    Set ws = Sheets("Dyno")
    If Err.Number <> 0 Then
        MsgBox "Dyno worksheet does not exist"
        Exit Sub

    Else
    Sheets("Dyno").Select
    
   End If
   
  ''FIND RANGES
  
  Dim rngTimes As Range
  Set rngTimes = Range("A1:GY1").Find("Time [s]")
  If rngTimes Is Nothing Then
    MsgBox "Time [s] column was not found."
    Exit Sub
    End If
    
  Dim rngLAMBDA As Range
  Set rngLAMBDA = Range("A1:GY1").Find("LAMBDA_A2D")
  If rngLAMBDA Is Nothing Then
    MsgBox "LAMBDA_A2D column was not found."
    Exit Sub
    End If
    
  Dim rngFeedgasTHCppm As Range
  Set rngFeedgasTHCppm = Range("A1:GY1").Find("Feedgas THC (ppm)")
  If rngFeedgasTHCppm Is Nothing Then
    MsgBox "Feedgas THC (ppm) column was not found."
    Exit Sub
    End If
    
  Dim rngMidBedTHCppm As Range
  Set rngMidBedTHCppm = Range("A1:GY1").Find("Mid-Bed THC (ppm)")
  If rngMidBedTHCppm Is Nothing Then
    MsgBox "Mid-Bed THC (ppm) column was not found."
    Exit Sub
  End If
  
  
 Dim rngTunnelTHCppm As Range
  Set rngTunnelTHCppm = Range("A1:GY1").Find("Tunnel THC (ppm)")
  If rngTunnelTHCppm Is Nothing Then
    MsgBox "Tunnel THC (ppm) column was not found."
    Exit Sub
  End If
  
  ''SELECT RANGES
  
    Dim r1, r2, r3, rT, rL, THCRange As Range
    Set r1 = Range(rngFeedgasTHCppm, rngFeedgasTHCppm.End(xlDown))
    Set r2 = Range(rngMidBedTHCppm, rngMidBedTHCppm.End(xlDown))
    Set r3 = Range(rngTunnelTHCppm, rngTunnelTHCppm.End(xlDown))
    Set rT = Range(rngTimes, rngTimes.End(xlDown))
    Set rL = Range(rngLAMBDA, rngLAMBDA.End(xlDown))
    Set THCRange = Union(r1, r2, r3, rT, rL)
    THCRange.Select
  
    selected_range = ActiveWindow.RangeSelection.Address
   
   ''PLOT GRAPHS

   Charts.Add
   
   ActiveChart.ChartType = xlXYScatterLinesNoMarkers
   ActiveChart.Name = "THC (ppm)"
   ActiveChart.SetSourceData Source:=Sheets("Dyno").Range(selected_range), PlotBy:=xlColumns
   ActiveChart.Location Where:=xlLocationAsNewSheet
    
   ActiveChart.PlotArea.Border.ColorIndex = 2
   ActiveChart.PlotArea.Interior.ColorIndex = 2
    
    ActiveChart.PlotArea.Border.LineStyle = xlAutomatic

    With ActiveChart.Axes(xlCategory)
        .HasMajorGridlines = True
        .HasMinorGridlines = False
    End With
    With ActiveChart.Axes(xlValue)
        .HasMajorGridlines = True
        .HasMinorGridlines = False
    End With
    
  
    With ActiveChart.Axes(xlCategory).MajorGridlines
        With .Border
            .ColorIndex = 57
            .Weight = xlHairline
            .LineStyle = xlDot
        End With
    End With
    With ActiveChart.Axes(xlValue).MajorGridlines
        With .Border
            .ColorIndex = 57
            .Weight = xlHairline
            .LineStyle = xlDot
        End With
    End With
    
    ''LAMBDA ON SECONDARY AXIS
    
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).AxisGroup = 2
    With Selection
    .MarkerStyle = xlNone
    End With
    
    ''COLOUR
    
    ActiveChart.SeriesCollection(1).Select
    With Selection.Border
    .ColorIndex = 5
    End With
        
    ActiveChart.SeriesCollection(2).Select
    With Selection.Border
    .ColorIndex = 1
    End With
    
    ActiveChart.SeriesCollection(3).Select
    With Selection.Border
    .ColorIndex = 3
    End With
    
    ActiveChart.SeriesCollection(4).Select
    With Selection.Border
    .ColorIndex = 4
    End With
    
    ''SCALE
    
    ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = 10
    ActiveChart.Axes(xlValue, xlSecondary).MinimumScale = 0
    ActiveChart.Axes(xlCategory).MaximumScale = 100
    ActiveChart.Axes(xlCategory).MinimumScale = 0
    ActiveChart.Axes(xlValue).MaximumScale = 2000
    ActiveChart.Axes(xlValue).MinimumScale = 0
    ActiveChart.SizeWithWindow = True

[RudiS]

How does the chart turn out if you manually draw it up in a file where the macro is not running correctly?
Does it also turn out incorrectly with the time on the Y-Axis?

[theherbalist]

Hmm good point, unfortunately I don’t have access to that file right now, but i just tried it in a fake spreadsheet that I just mocked up and both using the macro and manually drawing the graph works. It makes no sense (to me) whit it would work on some files but no others.
Thanks for your reply.

[RudiS]

If it makes no sense on your side, it is even foggier on my side.
It could help to post a desensitized copy of your file so we can assist further.
Without knowing your file structure and the formats of your data, we can only guess.

[theherbalist]

Yeah I thought that would be the case. I’ll try and post a desensitized version tomorrow or at the weekend. As I said I don’t have access to those files right now. however if anyone can spot anything blatantly wrong with my cod ethen feel free to let me know.