+ Reply to Thread
Results 1 to 12 of 12

Fill in cells with a value based on a neighbouring column

  1. #1
    Registered User
    Join Date
    03-03-2009
    Location
    Budapest
    MS-Off Ver
    Excel 2007
    Posts
    44

    Fill in cells with a value based on a neighbouring column

    Dear All,

    I attach a very simple excel file with an issue
    I have 2 columns, the second column has some empty cells in the end of the column
    What I would like to do is to fill in those empty cells but only as many as the filled-in cells in the first column

    (I have already tried the specialcells(xlCellTypeBlanks) but that did not work as the empty cells are not always "blank" as well)

    Could you please help?
    Many thanks
    Attached Files Attached Files
    Last edited by ppataki; 11-10-2009 at 06:41 AM.

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: Fill in cells with a value based on a neighbouring column

    How about something like this:

    Please Login or Register  to view this content.
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Registered User
    Join Date
    03-03-2009
    Location
    Budapest
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Fill in cells with a value based on a neighbouring column

    Hello!

    I get an "Application-defined or object-defined error" for the below part:
    If rCell.Offset(0, 1).Value = "" Then

    Could you please advise?
    Many thanks

  4. #4
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: Fill in cells with a value based on a neighbouring column

    You'll need to change the sheet name reference in the code according to what you have.

  5. #5
    Registered User
    Join Date
    03-03-2009
    Location
    Budapest
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Fill in cells with a value based on a neighbouring column

    Hello!

    I did that, now what happens is that only the last cell is filled in, the others do not. (screenshot attached)

    Could you please help?

    Thank you!
    Attached Images Attached Images

  6. #6
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: Fill in cells with a value based on a neighbouring column

    The code above works for me. Is it possible to post a workbook?

  7. #7
    Registered User
    Join Date
    03-03-2009
    Location
    Budapest
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Fill in cells with a value based on a neighbouring column

    Hi!

    I attach the 2 workbooks, the Follow up file is the one where data gets inserted, VBA pass is Vitessa
    Module2


    Thank you very much
    Attached Files Attached Files

  8. #8
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: Fill in cells with a value based on a neighbouring column

    You're only selecting the last cell

    Change this:
    Please Login or Register  to view this content.
    For this:
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    03-03-2009
    Location
    Budapest
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Fill in cells with a value based on a neighbouring column

    Hello!
    I changed it accordingly, but now I get an "Application-defined or object-defined error" for that line

  10. #10
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: Fill in cells with a value based on a neighbouring column

    Sorry,

    my fault - should have checked it.

    That error is generally down to not specifying addresses completely - try this:

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    03-03-2009
    Location
    Budapest
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Fill in cells with a value based on a neighbouring column

    Many many thanks, it works flawlessly now!!!

  12. #12
    Registered User
    Join Date
    08-26-2014
    Location
    New York, NY
    MS-Off Ver
    Windows8
    Posts
    1

    Re: Fill in cells with a value based on a neighbouring column

    001 XXX-XX-XXXX ACEVEDO, CARMEN
    HRA−HOURS TO LIMIT
    FISCAL YEAR WORKED (60)
    07/01/12 − 06/30/13 173.00 173.00
    07/01/13 − 06/30/14 1,538.50 1,538.50
    07/01/14 − 08/25/14 310.75 310.75
    001 xxx-xx-xxxx ACEVEDO, CLARA
    HRA−HOURS TO LIMIT
    FISCAL YEAR WORKED (60)
    07/01/12 − 06/30/13 1,363.50 1,363.50
    07/01/13 − 06/30/14 584.00 584.00
    07/01/14 − 08/25/14 45.25 45.25

    In column "B" I would like to fill blank cells only with the name of the employee. Is there a simple formula that can do that? I am a beginner in terms of VisualBasic. Thank you.

+ 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