+ Post New Thread
Results 1 to 8 of 8
MIS Systems Thread, Macros for excel - reporting - I need some help. in Technical; I have a report which uses colour and filters on numbers and I want to change the the filter on ...
  1. #1
    CatherineMay's Avatar
    Join Date
    Jun 2011
    Location
    Wigan
    Posts
    68
    Thank Post
    24
    Thanked 5 Times in 3 Posts
    Rep Power
    7

    Macros for excel - reporting - I need some help.

    I have a report which uses colour and filters on numbers and I want to change the the filter on the numbers. I want to change the numbers to the following:

    Green =10 to =15
    Amber =16 to =23
    Red =24 to =5000

    I have tried to follow the the manual to created a macro, however, every time I run the report I get a error message out of code.

    The sims report has forename, surname, reg, authorised absence, unauthorised absence and a blank column named Sessions Missed: the macro hides the two absence columns and shows the sum of the absence columns in the Sessions Missed.

    I've attached the excel file for the old report and wondered whether anybody could look at it and alter it for me. I know it is cheeky.
    Attached Files Attached Files

  2. #2

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,686
    Thank Post
    334
    Thanked 513 Times in 481 Posts
    Rep Power
    178
    I feel you're trying to overcomplicate this (Unless I've misunderstood) There are no macro's on that sheet. It's simple condition formatting.

    Highlight the cells, click conditional formatting, Manage rules, Select one (eg Green), and edit the rule. That's all you need to do I think.

    Attendance%20Profile%20Report(1).xlsx

    Like that? (Or do you need the macro, to add into the report? Can't you use the excel pre-formatted sheet as a template?)

    Steve
    Last edited by Steve21; 17th January 2012 at 08:32 AM.

  3. #3

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,843
    Thank Post
    671
    Thanked 1,381 Times in 1,143 Posts
    Rep Power
    349
    If you do use conditional formatting, you could start with a blank sheet, then record a macro of you applying the rules, this will then give you the correct code.
    You might need to save the file as a .xlsm file so we can see the macro if there's one in there already, or are you doing it from within sims so the macro is in the template file..

  4. #4
    CatherineMay's Avatar
    Join Date
    Jun 2011
    Location
    Wigan
    Posts
    68
    Thank Post
    24
    Thanked 5 Times in 3 Posts
    Rep Power
    7
    Thanks, the template is in SIMS, I can get the colours with no problem, however, the formula for the add the two columns give me a error message: Error 9 : Subscript out of range, and the cell shows: =SUM(RC[-2]:RC[-1]) instead the value of the formula. This is the first time I have used macro, really don't know I am doing!

  5. #5
    CatherineMay's Avatar
    Join Date
    Jun 2011
    Location
    Wigan
    Posts
    68
    Thank Post
    24
    Thanked 5 Times in 3 Posts
    Rep Power
    7
    I can't attach the file, would you send me an email address?

  6. #6

    Join Date
    Nov 2009
    Location
    Manchester
    Posts
    1,045
    Thank Post
    6
    Thanked 198 Times in 178 Posts
    Rep Power
    51
    So you just want the boxes to the following:

    24 to 50 to go Red
    16 to 23 to go Amber
    10 to 15 to go Green

    Automatically ofcourse?

  7. #7
    CatherineMay's Avatar
    Join Date
    Jun 2011
    Location
    Wigan
    Posts
    68
    Thank Post
    24
    Thanked 5 Times in 3 Posts
    Rep Power
    7
    Yes automatically - but the red is 24 to 5000

  8. #8

    Join Date
    Nov 2009
    Location
    Manchester
    Posts
    1,045
    Thank Post
    6
    Thanked 198 Times in 178 Posts
    Rep Power
    51
    O Ok, thats definitely just formatting, I'll change it and then pm you the file (assuming you can PM files about).

    Edit: I assumed wrong you cannot PM files, or i can't see it because the little icon has changed. Sent you a PM anyway
    Last edited by Achandler; 17th January 2012 at 09:59 AM.

SHARE:
+ Post New Thread

Similar Threads

  1. [SIMS] Macros for excel - reporting
    By jaky in forum MIS Systems
    Replies: 7
    Last Post: 6th January 2012, 02:17 PM
  2. need some help/advice
    By Inbir316 in forum Hardware
    Replies: 3
    Last Post: 4th February 2009, 02:14 PM
  3. Replies: 11
    Last Post: 7th March 2008, 02:28 PM
  4. Need Some Help Choosing A NAS Device
    By SYSMAN_MK in forum Hardware
    Replies: 8
    Last Post: 6th March 2008, 02:17 PM
  5. Drivers needed: some help please
    By contink in forum General Chat
    Replies: 4
    Last Post: 14th March 2007, 03:47 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •