Results 1 to 6 of 6
  1. #1
    Star Lounger SoonerJim's Avatar
    Join Date
    Aug 2011
    Location
    Temple, Texas USA
    Posts
    64
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Excel 2010 (Windows 7) Need help with macro to fill down a column

    Good Friday afternoon. I need to fill down a column with a set of six characters. The numbers are insulin doses. Four cells are the same, but the other two increase by 1 each time the sequence repeats. Here's an example:

    11 UNITS
    Y

    OI
    UNIT
    11

    The next six will look like this:
    12 UNITS
    Y

    OI
    UNIT
    12

    And the next six:
    13 UNITS
    Y

    OI
    UNIT
    13

    I need to go all the way down to 100 units. Then I can copy and paste the column into my hospital system instead of having to type it all out. I know I need a macro, but I'm not very good with writing macros on the fly. I can record them and even edit VBA if it's not too complicated. I have no idea even where to start. Does anyone have a suggestion? Thanks a lot!

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Sooner Jim,

    You don't need a macro. You don't even need a formula. If you are starting at row 1 with 1 unit, Just select the first six rows in the column then using the fill handle, drag down to row 600. The ## Units in row 1 and 6 will increment as long as you have the fill handle enabled in Advanced Options.

    If there is a space between the sets then select the first seven and copy down

    HTH,
    Maud

  3. #3
    Star Lounger SoonerJim's Avatar
    Join Date
    Aug 2011
    Location
    Temple, Texas USA
    Posts
    64
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Maud,

    I admit to amazement. I tried that procedure, but the list started out in the middle of the worksheet, and it wouldn't increment the fields. When I tried on a fresh worksheet at cell A1, it worked like a charm. Thanks so much. Have a great weekend!

    Jim

  4. #4
    Star Lounger SoonerJim's Avatar
    Join Date
    Aug 2011
    Location
    Temple, Texas USA
    Posts
    64
    Thanks
    2
    Thanked 0 Times in 0 Posts
    It worked so well, my boss wants me to do more. Is there a way to do this where the dosage goes up by fives (5 units, 10 units, 15 units?) I have to go all the way to 300 units! It doesn't seem to work naturally as it did the other day with 1 unit increments. Thanks a lot!

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    A nice solution from Maud.

    To increment in, say, multiples of 5:
    Set up your first 6 entries [A1:A6], e.g. A1=5, A6=5
    Then, in the next block of six, just use formulas to add 5 to the previous block
    e.g. in [A7] put the formula
    =A1+5
    in [A12] put the formulas
    =A7

    Now, highlight the range [A7:A12], and then use the fill handle to drag down your required number of 'blocks'
    After you have created your 'blocks' all the way to 300, you can then highlight the entire column and use copy-paste-values to convert the 'formulas' back to values if you want.

    zeddy

  6. #6
    Star Lounger SoonerJim's Avatar
    Join Date
    Aug 2011
    Location
    Temple, Texas USA
    Posts
    64
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you a lot!

Posting Permissions

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