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

**adtc** · 08-11-2011, 04:10 AM

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.

**royUK** · 08-11-2011, 04:14 AM

I'm not sure exactly what you want but maybe a Data Validation

**adtc** · 08-11-2011, 04:35 AM

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!

**watersev** · 08-11-2011, 05:46 AM

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

**adtc** · 08-11-2011, 06:22 AM

Perfect!
Thank you very much watersev! absolute legend!

**adtc** · 08-14-2011, 09:40 AM

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