Enter 1 of 2 values in a column conditional to another column

**duugg** · 07-30-2009, 12:44 PM

Hello all,

I would like a macro that does this

1. Find the "Customer Number" column by name

2. Find the range of the "Customer Number" column (bottom-most cell with data)

3. Find the column named "Date Shipped"

4. If there are any NON-Blank cells in the column named "Date Shipped", put the word "Sent" in the "Status" column (also needs to be found by name). This macro should stop when it is equal to the bottom-most cell in the "Customer Number" column.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

A couple of things I should mention..

1. The "Customer Number" column will always have the last row of data in it, so the macro search should stop when it gets to the last row of data in the "Customer Number" column.

2. All should be NOT be case sensitive

3. It should work on the active worksheet

4. I never know what column position these columns will be in, so the macro should search for column header by name.

5. Column headers will ALWAYS be in row 1.

Thanks much

**JBeaucaire** · 07-30-2009, 01:04 PM

This should get you going:

Please Login or Register  to view this content.

**JBeaucaire** · 07-30-2009, 01:10 PM

Or this version does it without having to "loop" at the end, should be a little faster overall.

Please Login or Register  to view this content.

**duugg** · 07-30-2009, 02:18 PM

Mr JBeaucaire,

Worked awesome!

Thanks much!

**duugg** · 07-30-2009, 03:23 PM

JB,

I just had another thought for this macro that will be of great benefit.

What code would I need to add to say "unsent" in the "Status" column if the cell in the "Date Shipped" column is blank?

Thanks,

duugg

**JBeaucaire** · 07-30-2009, 04:14 PM

Please Login or Register  to view this content.

**duugg** · 07-31-2009, 12:01 AM

Who needs a life when code like this exists! LOL

A BIG THANKS JB!

**JBeaucaire** · 07-31-2009, 01:56 AM

Well, between you and me, I wouldn't do this with a macro, I'd use a worksheet formula. Something like:

=IF(G2 > 0, "Sent", "Unsent")

**duugg** · 07-31-2009, 08:33 AM

Even if you don't know where the column position is? What if it changes it's position?

**JBeaucaire** · 07-31-2009, 11:36 AM

When using formula, I would enter the formula as needed, then copy it down. Should take all of 30 seconds. I'm just saying....but the macro is working, so that's good, too.