+ Reply to Thread
Results 1 to 12 of 12

After code has selected 6 cells randomly have the code sums up that row

  1. #1
    Forum Contributor
    Join Date
    01-06-2019
    Location
    Australia
    MS-Off Ver
    2019
    Posts
    185

    After code has selected 6 cells randomly have the code sums up that row

    Hello the code i have selects 6 different cells randomly for rows 6 - 30. Once it has done this i want it to sum the rows individually, and it has to meet >=111 and <=170. If possible change the background color of the cell if it meets >=111 <+140
    and a different cell fill color for >=141 to <=170.

    Please Login or Register  to view this content.
    Thank you for any help

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,869

    Re: After code has selected 6 cells randomly have the code sums up that row

    Hi there.
    With the code I see that you relate to shapes and similar objects.
    Have you tried to think that it's impossible for someone to rebuild that on a sheet to help you.
    Try and attach the file with the macro code, shapes and everything and also include a sample result of what you want to achieve.
    Don't send others down a guessing trail to get lost in all manner of answers.
    The 'random select' where do you randomize the numbers to be selected?
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Contributor
    Join Date
    01-06-2019
    Location
    Australia
    MS-Off Ver
    2019
    Posts
    185

    Re: After code has selected 6 cells randomly have the code sums up that row

    I lost my file became corrupt on a USB stick so I have attached a simplified version.

    As u can see I randomly select 6 cells and then sum them if the sum is not >=111 and <=170 then repeat the selection for that row
    if it does then move to next row (i+1)

    If possible to change the background colour of the cell for when the sum equals 111 - 140 then a gradient of that colour for values of the sum equaling 141 - 170?

    Thank you for your help
    Attached Files Attached Files

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,869

    Re: After code has selected 6 cells randomly have the code sums up that row

    Will see what I can do for you BUT

    Why does the dropdown only show 1s?
    Attached Images Attached Images
    Last edited by Keebellah; 03-19-2019 at 04:04 AM.

  5. #5
    Forum Contributor
    Join Date
    01-06-2019
    Location
    Australia
    MS-Off Ver
    2019
    Posts
    185
    Quote Originally Posted by Keebellah View Post
    Will see what I can do for you BUT

    Why does the dropdown only show 1s?
    I might have forgotten to fill series on the z12:z20

    It might have just copied. I thought i did fill series on CboxOdd

  6. #6
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,869

    Re: After code has selected 6 cells randomly have the code sums up that row

    Okay, at least I now know which range

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,869

    Re: After code has selected 6 cells randomly have the code sums up that row

    Looking at that I don't understand it, sorry

  8. #8
    Forum Contributor
    Join Date
    01-06-2019
    Location
    Australia
    MS-Off Ver
    2019
    Posts
    185
    Quote Originally Posted by Keebellah View Post
    Looking at that I don't understand it, sorry
    It is just to select how many in the array are odd numbers. It goes from 1 to 8. Sorry i wasnt home earlier.

    Think of it as having six numbers like the lottery randomly selected. You are controling how many are odd and even and what these 6 numbers add upto.

    If the randomly selected numbers dont add upto >=111 <=170 then re select that line till they fall in that range. Then move onto the next row/line. Does that help explain it at all?

    Think of the monon and tueson as different games, that could have 7 or 8 numbers selected using different named ranges for example a range might be 1-45, another 1-35 and 1-50

    So depending on the tab selected eg monon then u select allnumbers or partnumbers depending on the range u want to play. Then select how many odd numbers u want the rest will be even.
    Last edited by UltimateNeo; 03-19-2019 at 09:19 AM.

  9. #9
    Forum Contributor
    Join Date
    01-06-2019
    Location
    Australia
    MS-Off Ver
    2019
    Posts
    185

    Re: After code has selected 6 cells randomly have the code sums up that row

    Maybe something like this

    if cells(i,8).value ">=111", or "<=170" then
    next I
    else
    repeat loop
    end if

    not sure the proper syntax but something like that maybe?

    I want it to repeat the code until the condition is met

  10. #10
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,869

    Re: After code has selected 6 cells randomly have the code sums up that row

    Here's you file again.
    How about explaining it in a way others may understand?
    1. What are you selecting or doing with the drop-down lists.
    2. When you press the ENTER button? What happens.
    3. What Loop.
    I'm sure (and hope) YOU understand what you're asking, now only make it clear for the reader trying to help.
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    01-06-2019
    Location
    Australia
    MS-Off Ver
    2019
    Posts
    185

    Re: After code has selected 6 cells randomly have the code sums up that row

    1. CboxHot Is selecting a named range AllNumbers or PartNumbers. The CboxOdd is selecting how many Odd numbers will be in the array. The Array can be 1 - 8 numbers long. for example (1),(23),(21),(35),(15) and so on depending on how many the Cboxodd.value is.

    In H2 or cells(i,8) is the sum of 6 numbers that have been randomly selected and added up. With this number I want to be between >=111 and <=170 if not repeat the selection process until condition is met.
    as I am selecting 6 numbers per row starting at row 5 then finishing at row 28, the code needs to repeat for all rows

    If possible although might do with conditional formatting I want to change the background color to say green if the cell (H5) value is >=111 <=140 and then a gradation of that for the cell (H5) for if the value is >=141 <=170

    2. When you hit the enter key you should get the random selected cells pasted into B5:G29 each row will not have a repeated selection. so 33 cant appear twice in the same row. Column H sums up B5:G5 all the way down to B29:G29

    3. If you select TuesOn the same thing happens bu column H is Hidden and I becomes the seventh number and Column J becomes the sum column Sum(B5:G5,I5)

    For tuesOn the sum will be different from the >=111 <=170 criteria

    Hope that explains it better

  12. #12
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,869

    Re: After code has selected 6 cells randomly have the code sums up that row

    We'll see, will read and re-read to see if I understand.

+ 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. Code to color selected cells not working
    By kelseylm in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-23-2019, 12:17 PM
  2. [SOLVED] Recording New Macro For a Randomly Selected Range Of Cells
    By butterscotch in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-10-2015, 12:43 PM
  3. [SOLVED] VBA Code Change Event Code When 2 cells are selected
    By bjnockle in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-16-2014, 06:08 PM
  4. [SOLVED] Applying code to the 'selected' cells only
    By CXsjr in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-14-2013, 02:49 AM
  5. Code to insert rows between selected cells
    By DeepakS in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-07-2013, 08:18 AM
  6. Randomly choosing cells from a selected range
    By kjones505 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-19-2009, 12:56 PM

Tags for this Thread

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