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.

    [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]…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.

  • 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.

    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

  • Do you need to create the chart or just update it? The latter is the easier way. That way, you set it up as you want it.

    Then the user or you or your code would keep it updated. That is the power of named ranges. Of course if you want to stick with the original idea for setting rows and columns, that can still be done. Changes would update a named range. The next part is to create the chart by macro or not.

  • 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.


  • Found a solution

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!