Posts by johnnyjay123


    So I'm working on a spreadsheet that uses exported data from another piece of software. One field that is exported is Time and date, however I am running into problems with my code when trying to use the date in a calculation (I need to compare the date exported with todays date and get the answer in days and see if this is greater than 60 days - I can do this bit with proper dates but not with strings) because it appears as a text string in excel rather than a date.

    The dates appear in the following format 25/08/2017 14:00, so this would be 25th of Aug 2017 at 14:00 (dd/mm/yyyy hh:mm).

    I have found a piece of code which sort of works when trying to convert this to an actual date.

    Sub Date_test()
    Dim c As Range 
    For Each c In ActiveSheet.UsedRange.Columns("A").Cells
    c.Value = DateValue(c.Value) 
    Next c
    End Sub

    But doesn't convert everything correctly, as sometimes the date could represent a US or UK date. E.g 08/09/2017, could either be 08 Sept 2017 (correct) or 09 Aug 2017 (incorrect- for me) Is there a way (through VBA), that I can convert the text string I have into a date excel will recognise and in the format dd/mm/yyyy (time isn't important), without converting it to a mm/dd/yyyy format accidently? Thanks

    Found a solution

    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.


    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

    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.


    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]