Setting the x-axis using VBA on a variable plot

Hi

So I have the following VBA code but I'm a bit stuck now. At work i use bits of equipment that export data for various parameters, the column titles are in row 1 with the data below it (normally 5000-10000 rows). The titles are always the same but the order in which they appear in the excel file varies (so I cannot write a code which always uses the same range). Below is part of a larger code (I've managed to get the rest working) but need help with this part.

Sub Test()
With Sheets("Sheet1") 


a = .Range("A21").Value 'Row that the first data point is in
b = .Range("A22").Value 'Column that the first data point is in 
c = .Range("A23").Value 'Row that the last data point is in
d = .Range("A24").Value 'Column that the last data point is in 
End With


ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(Sheets("Sheet1").Cells(a, b), Sheets("Sheet1").Cells(c, d))
End Sub

[FONT="Arial"]This code sets cell A21 as the row and A22 as the column of the first data point and A23 as the row and A24 as the column of the last data point of a selected parameter that I want to plot. This works and I am able to plot these values on a graph. However I can't seem to work out how to have them plotted against the X values (usually time). What I would like to be able to do is use cell A21-A24 as the first and last rows/ columns as the y values and A27:A30 as the X values and use this to set the source data (Note A21:A24 and A27:A30 would not contain the data that I want to plot, it would contain the row/ column ranges from the main data table, which is what would get plotted (they would only be reference cells). A link to the spreadsheet can be found here, the section highlighted in yellow has not been used in the above code but this is what I would like to inlcude. The plot at the top of the page shows the heart rate values plotted on the graph however they aren't plotted against anything, just the order that they are in. The second plot is what I would like the VBA code to be able to do, here heart rate is plotted against the associated time values. Hope that makes sense, thanks for your help. [/FONT] https://docs.google.com/spread…9dUel0tE/edit?usp=sharing[FONT="Arial"][/FONT]

Thanks for your suggestion, i'll have a look into it.

Also managed to get my original code working (wasn't sure how to edit my 1st post) new code below.

Sub test2()


    With Sheets("Sheet1")
    'y values
        a = .Range("A21").Value
        b = .Range("A22").Value
        c = .Range("A23").Value
        d = .Range("A24").Value
    End With


        With Sheets("Sheet1")
    'x values
        e = .Range("A27").Value
        f = .Range("A28").Value
        h = .Range("A29").Value
        i = .Range("A30").Value
    End With




ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(Sheets("Sheet1").Cells(a, b), Sheets("sheet1").Cells(c, d))
ActiveChart.SeriesCollection(1).XValues = Range(Sheets("Sheet1").Cells(e, f), Sheets("sheet1").Cells(h, i))
End Sub

Hi, hoping to get some more help with the same problem above. I adjusted my code to use named ranges instead, however I don't seem to be able to plot the values anymore when referencing the named ranges, can you give me an example of how the code should look with regards to setting the source data.

Sub test3()
Dim column_headers As Range
Dim x_axis As Range
Dim y_axis As Range


Set column_headers = Range("1:1") 'Look in entire first row for required column headers.
Set x_axis = column_headers.Find("Time") 'Find time in row 1 and set as x axis value
Set y_axis = column_headers.Find("Heart rate") 'Find Heart rate in row 1 and set as y axis value


'setting x -Axis range
x_axis.Offset(1, 0).Select
Set x_axis = Range(ActiveCell, ActiveCell.End(xlDown))


'setting y -Axis range
y_axis.Offset(1, 0).Select
Set y_axis = Range(ActiveCell, ActiveCell.End(xlDown))




ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SetSourceData Source:=Range("y_axis")
ActiveChart.SeriesCollection(1).XValues = Range(x_axis)




End Sub

When using the code above the y values are plotted but I get an error 'Application-defined or object defined error' but the x values aren't plotted.

Please can you advise on how to adjust my code so it doesn't give the error and the y values are plotted against the x values instead of just the order they appear in the table.

Thanks for your help

I need to create the chart (I think unless what I need can be done with an existing chart and named ranges already set up?), the spreadsheet will be blank and the user will copy their data into it each time, then they can select what needs plotting.

However the variables which need to be plotted will usually be in different columns (due to what data has been exported and the order it has been done, so I don't know if I can set up a named range for something who's position is variable, this is why I'm setting it in the code), and I wanted to eliminate the amount of work the user had to do.

Can you let me know how I would set the source data to the data I want plotting for both X and y axis using named ranges, rather than the cell references I was using in the first post.

Edit: forgot to add there will be about 15-20 variables which may or may not need plotting, so thought the code would be neater if the user could specify what they needed rather than covering every option.

Thanks

Quote from Kenneth Hobson

Of course if you want to stick with the original idea for setting rows and columns, that can still be done.

How would I go about doing this?

Found a solution

Sub Test()


Dim col_headers As Range
Dim variable1 As Range
Dim variable2 As Range


'Set the search area
Set col_headers = Range("1:1")


'Find the column header 'time' and set it to the 1st variable
Set variable1 = col_headers.Find("Time")


'Find the column header 'Heart rate' (in row 1) and set it to the 2nd variable
Set variable2 = col_headers.Find("Heart Rate")


'set the x -Axis range (variable1)
variable1.Offset(1, 0).Select
Set variable1 = Range(ActiveCell, ActiveCell.End(xlDown))


'set the y -Axis range (variable2)
variable2.Offset(1, 0).Select
Set variable2 = Range(ActiveCell, ActiveCell.End(xlDown))


'add chart
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SeriesCollection(1).Values = variable2
ActiveChart.SeriesCollection(1).XValues = variable1


End Sub