+ Reply to Thread
Results 1 to 11 of 11

Need to fill a grid of cells with data, based on percentage amount specified.

  1. #1
    Forum Contributor
    Join Date
    04-18-2009
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    161

    Need to fill a grid of cells with data, based on percentage amount specified.

    Been a long time since I have had to ask for help on here, but I have always had my problem solved here. I'm working on a project that is almost done except for one thing I would like to be able to do.

    Here is the task

    I will have a grid of cells 10X12 that is 120 cells total b37 thru n46

    above this grid i will have 4 cells with data entered . In the cells beside the data i will
    enter the percentage of boxes or cells i want this particular data to fill ( I show this at bottom). So say for
    TRI i want to fill 10% of the grid cells so i would like for the code to fill 12 cells with TRI. TRT i want to fill 20% so i need to fill 24 cells with TRT. This goes on until all cells in the grid get filled or populated. this data can go into the cells anyway that makes it easiest. I am going to randomize the data once i have all boxes in the grid populated. Thanks for any help that anyone might offer.

    TRI 10%
    TRT 20%
    BRI 25%
    BRT 45%

  2. #2
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Need to fill a grid of cells with data, based on percentage amount specified.

    Hi Traymond,

    Here's a suggestion below. There is probably a better / simpler way to do it but I came out with this.
    Simply change the range reference (D1, D2, D3, D4) where you have your TRI, TRI, BRI, BRT data in the "Test" macro.


    Please Login or Register  to view this content.
    GC Excel

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  3. #3
    Forum Contributor
    Join Date
    04-18-2009
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    161

    Re: Need to fill a grid of cells with data, based on percentage amount specified.

    That is awesome GC Excel !!!!! works like a charm, thanks so much.

  4. #4
    Forum Contributor
    Join Date
    04-18-2009
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    161

    Re: Need to fill a grid of cells with data, based on percentage amount specified.

    Hey GC Excel,

    was wondering if you could help with a couple adjustments, I have been trying but not having any luck. I came to realize that in time I may have instances where I will need to work off of more
    than the four cells where I have data ( the TRI,TRT,BRT,BRI) I also been made aware this data could be different at times like (TRB,COG,LMQ ETC..) So I need to allow for six boxes of data and change the code where you are specify the text For i = 1 To n1: ar(i) = "TRI": Next I and change the "TRI" to the cell text because this can change. I list below where my data cells will exist. thanks for any help you might offer


    c26 d26 e26 f26 g26 h26
    TRT TRI BRT BRI DRA DBI

    C27 D27 E27 F27 G27 H27
    10% 20% 30% 10% 20% 10%

  5. #5
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Need to fill a grid of cells with data, based on percentage amount specified.

    Hi Traymond,

    Here's an improve version of the macro allowing you to have six boxes of data...
    Let me know if you have any questions.

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    04-18-2009
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    161

    Re: Need to fill a grid of cells with data, based on percentage amount specified.

    GC Excel , thanks so much for that last code . that is awesome ! I hate to be a pest , you have done enough already ..... but could I ask for a small change?

    instead of using the range C26:H27 could you change the code to run vertical in the range B16:b21 my percentages will be in C16:C21.

    thanks so much again for what you have done already.

  7. #7
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Need to fill a grid of cells with data, based on percentage amount specified.

    Hi Traymond,

    No problem... Simple modification required. See below

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    04-18-2009
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    161

    Re: Need to fill a grid of cells with data, based on percentage amount specified.

    perfect !!!!! thanks so much for the help.

  9. #9
    Forum Contributor
    Join Date
    04-18-2009
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    161

    Re: Need to fill a grid of cells with data, based on percentage amount specified.

    GC Excel, I am getting error sometimes when running the code . it seems like it happens when I have more than one cell with single digit percentage like 5% , 6% etc.

    it stops in the code that fills the array . It stops on this line of code ar(i) = arData(1, j)

    I had to make some changes to my grid. it is 150 cells now instead of 120 . I also changed the range to look at 12 cells instead of 6. I made the changes in the code ... changed 120 to 150
    in the code where it appeared , and changed the mod from 12 to 15 . just wanted you to be aware . can you help?

  10. #10
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Need to fill a grid of cells with data, based on percentage amount specified.

    Hi Traymond,

    Here's an updated version. Let me know how it goes.

    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    04-18-2009
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    161

    Re: Need to fill a grid of cells with data, based on percentage amount specified.

    I think that fixed it ! thanks for the help
    Last edited by Traymond; 08-25-2016 at 08:18 PM.

+ 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. Automatic percentage calculation that shows the amount of cells changed in colour
    By johnson123 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-24-2015, 06:55 AM
  2. [SOLVED] Calculate number/percentage less than x amount based on text in another column
    By dvs in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-17-2013, 10:10 AM
  3. Replies: 2
    Last Post: 12-15-2011, 04:14 AM
  4. Auto fill cells based on the data in another cell
    By excelnewman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-08-2011, 01:29 AM
  5. [SOLVED] random numbers to fill a certain amount cells in a row
    By bunjijumper in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-27-2010, 08:15 AM
  6. Price Grid with Percentage Change
    By Anthrax in forum Excel General
    Replies: 3
    Last Post: 03-12-2008, 02:40 PM
  7. Replies: 3
    Last Post: 03-11-2008, 01:01 PM

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