+ Reply to Thread
Results 1 to 10 of 10

Autofill a column based on number in adjacent column

  1. #1
    Registered User
    Join Date
    03-14-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2003
    Posts
    7

    Autofill a column based on number in adjacent column

    Hi everyone,

    I'm trying to create a macro which will autofill column C with data located in column A and column B. Column A contains values while column B contains the # of instances that each value occurs in the data set. I want column C to autofill with values from column A based on the # of instances that the value occurs in column B. So for example if the value is 1.5 and the # of instances is 3, I want column C to autofill the value of 1.5 for 3 cells.

    I have attached an excel file for you to look at. I'm very familiar with using functions in excel but I'm learning how to write macros for the first time. Thanks for reading and I hope to hear from you all soon!
    Attached Files Attached Files
    Last edited by fbonetti; 04-08-2011 at 04:07 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Autofill a column based on number in adjacent column

    Please Login or Register  to view this content.
    .
    .
    VERSION (2) ' THIS VERSION ASSUMES ALL VALUES IN COLUMN B ARE POSITIVE NUMBERS

    Please Login or Register  to view this content.
    Last edited by nimrod; 04-08-2011 at 03:17 PM.

  3. #3
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Autofill a column based on number in adjacent column

    This should do what you are looking for. I've commented the code so you can see what each step does.

    Please Login or Register  to view this content.
    Is your code running too slowly?
    Does your workbook or database have a bunch of duplicate pieces of data?
    Have a look at this article to learn the best ways to set up your projects.
    It will save both time and effort in the long run!


    Dave

  4. #4
    Valued Forum Contributor
    Join Date
    02-12-2011
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    817

    Re: Autofill a column based on number in adjacent column

    This will do the trick.
    Please Login or Register  to view this content.
    Edit: to late
    Last edited by HSV; 04-08-2011 at 03:12 PM. Reason: to late
    Kind regards, Harry.

  5. #5
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Autofill a column based on number in adjacent column

    please check attachment, run code "test"
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Autofill a column based on number in adjacent column

    VERSION (2) ' THIS VERSION ASSUMES ALL VALUES IN COLUMN B ARE POSITIVE NUMBERS

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by nimrod; 04-08-2011 at 03:24 PM.

  7. #7
    Registered User
    Join Date
    03-14-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Autofill a column based on number in adjacent column

    Thank you all so much for the help! I'm going to go with nimrod's code since it appears to be the most simplistic. My issue is ALMOST solved, however I have one more question. How can I make it so that it looks at a specific range within column B? I want it to look at column starting with B6. And likewise how can I make it autofill data into a range in column C? I replaced line 2 with this code but I received an error:

    For Each iCount In Columns("B6:B").SpecialCells(xlCellTypeConstants, 1)

    I also tried this (to no avial):

    For Each iCount In Range("B6:B").SpecialCells(xlCellTypeConstants, 1)

    Thanks again for your help!

  8. #8
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Autofill a column based on number in adjacent column

    Each iCount In Range("B6:B60000").SpecialCells(xlCellTypeConstants, 1)

    this would be a range for B6 to B600000... within this range it only goes cells that have a constant that is a number

  9. #9
    Registered User
    Join Date
    03-14-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Autofill a column based on number in adjacent column

    You're the best

  10. #10
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Autofill a column based on number in adjacent column

    Here's a sample with the "Range" specified ( as per your request) ... this sample also has the results starting at the top of column C. This sample also assumes that all numbers in Column B are positive.

    Please Login or Register  to view this content.
    Last edited by nimrod; 04-08-2011 at 04:24 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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