Posts by CAPRICE63

    see Debra's excellent explanation :


    https://contextures.com/xlNames01.html#Dynamic


    Thanks for your reply. On first glance it looks like Debra's explanation is ok for one table with ranges set up for one set of rows.
    I'll have a deeper look at it but my problem is... I pull the raw data, into one table, from ACCESS every day, I then need to create all the ranges based on the Time of each race (so my WSheet could have around 40 ranges). So, I need macros to set up all the ranges.
    I think I'm reliant on macros though.

    Hi,
    Hoping for some help please.
    I am trying to use some VBA based on that posted here:
    https://www.ozgrid.com/forum/forum/h...288#post905288


    I've been bashing my head trying to get my VBA to work.
    I have data which is grouped by date/time. Each group of date/time data has several columns of numerical values that I need to colour code (red to green) but I also need to highlight the top 2 and bottom 2 values e.g. with a Black border or change the text colour or something similar. I have found out how to colour code the data (from searching various forums) but when I try to highlight the top/bottom 2 using my code based on the code by https://www.ozgrid.com/forum/member/75013-yegarboy I keep getting errors.


    Here's what I've got [INDENT]

    [/INDENT]




    My full code with the Red to Green colour-coding is [INDENT]

    [/INDENT]




    I get the error message "Run-time ..1004. Unable to get the Large property of the WSFn class"


    And the VBA highlights this section:
    "If zCell = Application.WorksheetFunction.Large(rng, 1) Then"


    NOTE: The code seems to run through once with k=10 as the some of the first date/time data gets colour-coded and some data also appears to get highlighted in bright green as shown in picture:
    [ATTACH=JSON]{"data-align":"none","data-size":"full","title":"DB.jpg","data-attachmentid":1222915}[/ATTACH]


    This pic is not how I want it to appear. I am just including it to show you what happens up to the error.


    I'm no pro. Most of my code is cut and paste from forums so it may not be as elegant as it could be.
    I'd be grateful for any suggestions as to why I get errors and also for any more elegant solutions to my code.
    Thanks for taking the time to help.

    Hi,
    I need some help please to be able to quickly sort a table on "Date / Time" and (firstly) another column (F1). Then I need to have the top 3 items of F1 (for each unique Date / Time) marked as A, B and C in Column T3F1.
    (See attached file).
    It would be extremely useful if the unique "Date/Time/F1" set of data could be conditionally coloured with Red to Green scale. I've just updated the attached file to show some of the colouring applied.



    After the Date/Time/F1 sort, I then need to sort table on Date/Time/F2. Then I need to have the top 3 items of F2 (for each Date Time) marked as 1/2/3 in Column T3F2.
    Again, it would be extremely useful if the unique "Date/Time/F2" set of data could be conditionally coloured with Red to Green scale.


    I need to repeat this for F3 and F4 also.


    This takes too long to do manually and I've looked at trying Index/Match in formulas but can't get my head around it.


    I tried recording macros but can't seem to get anything working since the ranges are dynamic owing to the filters.


    I'm sure it just needs a VBA that loops on each filter but my VBA skills are minimal.


    Any help would be gratefully received.


    PS. The ABC entries need to have a condition applied - eg .... IF([@F2]>E3*1.333,"A*","A").


    PPS. It would also be useful to have the Top 3 items colour coded (RAG) within the VBA. However, this is not critical as I know how to apply colour scales easily.

    Hi Pike,
    Thank you for the file. Unfortunately, I get a run-time error. (see screen-shot).
    Also, I think I understand the structure of the VBA (although I don't know the syntax of it all - as I am not trained in VBA).
    I may have not understood everything so forgive me if I am wrong here.... What I see is that:
    1. The first part of code sets up the array names etc.
    2. The next part of code somehow counts how many new tables will be created. If >40 then message. However, in the example data, there are only 17 entries in the Activity column so I would not expect to see the message but it shows on screen.
    3. Next, the code loops to filter each Day/Room/Time and set-up a new table in sheet 2 for the Day/Room/Time/Name where there is an Activity on that day. It continues to loop to find the next Activity on that day then comest out of the nested loop to loop for the next day etc...


    The VBA is seems to be intended to create new separate tables for each unique Day/Room/Time that every activity took place. I the example data this would create 17 Tables.


    This could result in thousands of tables owing to the large amount of data (2000 pupils per time-slot every day for a year).


    I was hoping that there was a way to select one of the "Activity" entries (e.g. quiet) from the table and create a new table that shows all the data relevant to the TIME of the Activity.
    e.g. selecting "quiet" will create a table showing the all the Day, Room, Time, Name, Activity information for times 09:00, 10:00 and 13:00. This could be contained in one new table or, at most, would require 3 new tables (one for each unique Day/Room/Time).


    Thanks for your help.
    Kind regards
    Chris

    Example of sample file attached:


    Hi,
    I'm looking for best way to select one of the "Activity" entries (art, numeracy, play, quiet, reading, science) from the left table and create a new table (like the one on the right) that shows all the data relevant to the time of the Activity.
    e.g. selecting "quiet" will create a table showing the all the Day, Room, Time, Name, Activity information for times 09:00, 10:00 and 13:00.


    Background - The NEW table will show all the names of pupils that were in the Room at the time that pupil "Good 65", "Good 2" and "Good 101" were doing a "quiet" Activity.


    I'm looking to use quickest method (probably VBA) owing to the large amount of data (2000 pupils per time-slot every day for a year). Tables to be created on a daily basis sometimes several times a day.


    Also, it would be preferable to create the new table on a new worksheet or in a new workbook that can be circulated to staff.


    I am not VBA educated but I can follow code.


    Thanks for taking the time to consider my problem.


    Microsoft Excel for Mac 2011 V. 14.7.7