+ Reply to Thread
Results 1 to 11 of 11

For-Looping with Range.Offset - Not Getting Expected Result

  1. #1
    Registered User
    Join Date
    08-07-2010
    Location
    Canberra, Australia
    MS-Off Ver
    Excel 2010
    Posts
    13

    For-Looping with Range.Offset - Not Getting Expected Result

    I'm trying to develop an Excel application that records judges' scores for dance competitions.

    Judges enter scores against 10 criteria classified in three categories.
    If perfect, each category would add up to 20 points and hence the perfect total score will be 60.
    In order for a competitor to obtain qualifier for a title, two out of three judges must give at least 10 for all three categories and at least 45 for the total.

    I've developed the application so far that the scores entered in userform calculates (upon click of "Calculate" button) and transfers the scores to a worksheet (upon click of "Submit" button).

    I would like to have the cells of category subtotal scores with values less than 10 highlighted with light yellow colour and the same with total score cells less than 45.

    The following code would do what I want for the less-than-45 part but not for the subtotal-less-than-10 component.
    Whenever there is a subtotal less than 10, it highlights the right-most cell in the row with ID number.

    Please Login or Register  to view this content.
    I'm not totally versed with the use of array and I'm thinking that's where I'm not doing right but don't know how to correct it.
    Could someone please help? I've attached a file that describes what I'm doing.

    Thanks heaps.
    Maki
    Attached Files Attached Files

  2. #2
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: For-Looping with Range.Offset - Not Getting Expected Result

    Hi Maki,
    I believe this is quite possible with the help of conditional formatting.
    Provide some example data to work with.
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com

    Excel is not a matter of Experience, its a matter of Application.

    Say Thanks, Click * Add Reputation

  3. #3
    Registered User
    Join Date
    08-07-2010
    Location
    Canberra, Australia
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: For-Looping with Range.Offset - Not Getting Expected Result

    Hi Vikas,
    Yes, I sometimes do wonder whether I'm over processing, trying to do everything through vba...
    I am, however, planning to distribute the final product to multiple users and would prefer to have most processing done behind the scene.
    Besides, it helps me understand Excel VBA better if I could do things differently from "same old" Excel functions.

    Would you download the file (Experiment.xls) attached to my initial post:
    1. open it with enabling macro;
    2. click on "Show Userform" button;
    3. enter numbers in the 10 score boxes, not exceeding the "Maximum Point" on the left;
    4. click on "calculate" button; (this will automatically fill in subtotal boxes)
    5. click on "submit" button;
    6. close the form and look at Sheet2 worksheet?

    It would help to see how it goes if you deliberately enter scores that make subtotal(s) less than 10 or total score less than 45.

    Many thanks for taking your time to look at the issue.

    Maki

  4. #4
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: For-Looping with Range.Offset - Not Getting Expected Result

    I cant get it to run on mine, but, looking at the code, I have a question about this line....
    Please Login or Register  to view this content.
    Should the i in the code be a number?
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  5. #5
    Registered User
    Join Date
    08-07-2010
    Location
    Canberra, Australia
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: For-Looping with Range.Offset - Not Getting Expected Result

    I meant i to be a variable (declared as integer) and loop nextrow.Offset(0, 1) to nextrow.Offset(0, 3), where nextrow is column C and ID number goes.
    In the attached workbook, this means values in column D to F are checked and if any are under 10, that cell will have light yellow fill colour.

    Clearly this isn't working but what am I doing wrong?

    Maki

  6. #6
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: For-Looping with Range.Offset - Not Getting Expected Result

    I will look into more, but without being able to run it on my PC, don't know how much help I can be. Whenever I try to run the code, I get errors.

  7. #7
    Registered User
    Join Date
    08-07-2010
    Location
    Canberra, Australia
    MS-Off Ver
    Excel 2010
    Posts
    13

    Exclamation Re: For-Looping with Range.Offset - Not Getting Expected Result

    Hi Tom (or anyone else who had a go), did you have any luck?
    Were you at least able to see the codes in the editor and decipher what was causing the error you got?

    I initially posted the file in xls format for earlier version users but here's one in xlsm (Experiment.xlsm) format for those who prefer macro-enabled newer version.

    Also attached is a zipped file of ExperimentUserForm1.frm and ExperimentModule1.bas (ExperimentImportables.zip) if all else fails. Please start a new workbook, open VBA editor and import the two files after unzipping them.

    With this approach,
    1. first run ShowForm macro.
    2. When you see the form, fill 10 score boxes with numbers not exceeding Maximum Points shown on the left.
    3. Click on "Calculate" button (Subtotal boxes will be filled in with this action).
    4. Click on "Submit" button.
    5. Repeat 2. to 4. a few times and
    6. click "Close", this should show Sheet2

    You'll be looking at Sheet2 with some numbers at this stage.
    Column C is autonumbering ID starting in cell C2 with 1.
    Column D is subtotal of Routine Content, E is subtotal of Technical Merit, F subtotal of Musical Interpretation and G the total of all three subtotals.

    I want cells in columns D, E, F with figures less than 10 to be highlighted with light yellow colour but currently this is not happening and instead all ID cells in column C are highlighted.
    (I also want total scores less than 45 be highlighted and this part IS working.)

    Could anyone please have a look and let me know what I'm doing wrong?
    I'm getting a little desperate.

    Thanks heaps.

    Maki

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: For-Looping with Range.Offset - Not Getting Expected Result

    See if the attached is close to what you are after.
    Attached Files Attached Files
    Last edited by jindon; 01-15-2015 at 01:13 AM. Reason: Add Validation for each textbox

  9. #9
    Registered User
    Join Date
    08-07-2010
    Location
    Canberra, Australia
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: For-Looping with Range.Offset - Not Getting Expected Result

    Thank you, Jindon, for taking your time to do the coding.

    Unfortunately, I need to be able to store all the scores in a worksheet and process it further to come up with a fancy leader board.
    So different colours, depending on the values, showing only in the userform won't do it for me.

    This Experiment.xls(m) is an abbreviated version of the entire application.
    In real life, there will be a numerous competitors competing in various classes and each competitor will be judged by 3 judges.
    So all these records will have to be stored, processed and possibly displayed somehow in worksheets.

    Still, many thanks for your time. I will look into your codes further to see if I can use some techniques in mine.

    Maki

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: For-Looping with Range.Offset - Not Getting Expected Result

    OK, this should take care of the submit action.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    08-07-2010
    Location
    Canberra, Australia
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: For-Looping with Range.Offset - Not Getting Expected Result

    Wow, thank you so much, jindon. This certainly IS what I wanted.

    Lots to take in, though. I've never used class module so good opportunity for me to take a good look.

    Thanks again.

    Maki

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Getting zero when a non-zero result is correct and expected.
    By fred3 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-30-2014, 12:05 AM
  2. Macro returns expected result
    By cossie2k in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-09-2012, 08:25 AM
  3. VLookup not returning expected result
    By tenscourts in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-20-2012, 10:04 AM
  4. COUNTIF not returning expected result
    By zpenacho in forum Excel General
    Replies: 3
    Last Post: 01-20-2012, 06:37 PM
  5. SUMIF - not returning expected result
    By Paul Sheppard in forum Excel General
    Replies: 4
    Last Post: 08-16-2011, 11:43 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1