+ Reply to Thread
Results 1 to 9 of 9

Macro to paste value, based on Tick-Boxes, if adjacent cell is non-blank

  1. #1
    Registered User
    Join Date
    08-01-2011
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2007
    Posts
    12

    Question Macro to paste value, based on Tick-Boxes, if adjacent cell is non-blank

    Hi,

    I have attached a workbook to illustrate.

    I need a macro that will copy the Agent's name (associated with target tick-box) to column A (beginning at certain range and moving down - so that it doesn't over right the same cell every time) if the adjacent cell has a value.

    Each time this is used, the tick-boxes, and hence the Agent name, will be a different combination. Also there will be a varying number of "orders".

    Ideally I need it to take the names (5 boxes are ticked for example), and evenly paste them next to the orders, and when the are no more orders the pasting stops.

    It would look something like this:

    Agents Orders
    Agent2 1-123
    Agent8 1-124
    Agent11 1-125
    Agent12 1-126
    Agent13 1-127
    Agent18 1-128
    Agent2 1-129
    Agent8 1-123
    Agent11 1-123
    Agent12 1-124
    Agent13 1-125
    Agent18 1-126
    Agent2 1-127
    Agent8 1-128
    Agent11 1-129
    Agent12 1-123
    Agent13 1-124
    Agent18 1-125
    Agent2 1-126
    Agent8 1-127
    Agent11 1-128
    Agent12 1-129
    Agent13 1-123
    Agent18 1-123
    Agent2 1-124
    Agent8 1-125
    Agent11 1-126
    Agent12 1-127
    Agent13 1-128
    Agent18 1-129
    Agent2 1-269
    Agent8 1-127


    I have experimented with macros and loops, but am still very new to it all.

    Any help would be greatly appreciated.
    Big thanks in advance!

    adtc.
    Attached Files Attached Files
    Last edited by adtc; 08-14-2011 at 10:33 AM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Macro to paste value, based on Tick-Boxes, if adjacent cell is non-blank

    I'm not sure exactly what you want but maybe a Data Validation
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    08-01-2011
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Macro to paste value, based on Tick-Boxes, if adjacent cell is non-blank

    Hi Roy,

    thanks for your prompt response.
    I had a look at "Data Validation", but it doesn't seem like it will do quite what I'm after.

    Sorry If I was a bit ambiguous (sounded right in my head lol).

    If you look at the work book, I need the macro (which is attached to a click button for example) to paste the names in to colum A26:$A next to the orders (in column B26:$B) ONLY if the name is selected (using the text box).

    The distribution should be even, so that every agent has 5 orders (assuming 5 tick-boxes are selected and there are 25 orders). Not sure if this can be done in chucks/groups or as a loop.

    Each day, the number of agent (selected tick-boxes) and number of orders will change.

    I may be looking at it from the wrong perspective, so if you know a better/more simplistic way to do it, please feel free to say so.

    I hope this is easier to understand.

    Thanks again for your time!

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

    Re: Macro to paste value, based on Tick-Boxes, if adjacent cell is non-blank

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

  5. #5
    Registered User
    Join Date
    08-01-2011
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Macro to paste value, based on Tick-Boxes, if adjacent cell is non-blank

    Perfect!
    Thank you very much watersev! absolute legend!

  6. #6
    Registered User
    Join Date
    08-01-2011
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2007
    Posts
    12

    Question Re: Macro to paste value, based on Tick-Boxes, if adjacent cell is non-blank

    Hi watersev,

    I'm trying to get the end result to look like the attached workbook.

    When you click the button to run the marco, it should fill the selected (tickbox) agent's names in column A26 on-ward and fill "Not Actioned" in column C26 onward Only if that row has a value in Column B of the same row.
    At present, it fills the column A, but not C.

    Thanks again for your help.
    Please let me know if I need to clarify anything.

    Adtc
    Attached Files Attached Files

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

    Re: Macro to paste value, based on Tick-Boxes, if adjacent cell is non-blank

    please check attachment, run code "test"
    Attached Files Attached Files
    Last edited by watersev; 08-14-2011 at 10:09 AM.

  8. #8
    Registered User
    Join Date
    08-01-2011
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Macro to paste value, based on Tick-Boxes, if adjacent cell is non-blank

    works perfectly, thank you very much!

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

    Re: Macro to paste value, based on Tick-Boxes, if adjacent cell is non-blank

    if it's ok, please mark the thread as solved, see Forum Rules for details

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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