Tiering Macro, Should be easy?
 

Hi I need to write a macro that I believe is pretty easy but I'm a
little rusty on my programming. Any help would be greatly appreciated.

I have a list like this:
name1 25
name2 50
name3 70
etc

I need to go down the numbers column (column B). If the number is
between 20 and 30, I want to insert ".1" in a third column on the
right. If the number is between 30 and 100, I want to insert ".2".
else, I want to insert "5".

Thanks in advance!

Tiering Macro, Should be easy?
 

Turn on the macro recorder (Tools | Macro Record new macro...),
select the first cell in column C with data in column B. Suppose it is
C1. Enter the untested formul
=IF(OR(B1<20,B1200),5,IF(B1<=30,0.1,0.2))
Move the mouse to the lower right corner of the cell (it will become a
black cross) and double-click. This will copy the formula as far down
column C as there is data in B.

If you don't want to leave the formula in place, select column C, copy,
right-click, select Paste Special... | Values.

Turn off the recorder and XL will give you the necessary code.

You may also want to see
Beyond Excel's recorder
http://www.tushar-
mehta.com/excel/vba/beyond_the_macro_recorder/index.htm#eg4

particular Example 4: Add a formula to a range of cells

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article .com,
says...
Hi I need to write a macro that I believe is pretty easy but I'm a
little rusty on my programming. Any help would be greatly appreciated.

I have a list like this:
name1 25
name2 50
name3 70
etc

I need to go down the numbers column (column B). If the number is
between 20 and 30, I want to insert ".1" in a third column on the
right. If the number is between 30 and 100, I want to insert ".2".
else, I want to insert "5".

Thanks in advance!

Tiering Macro, Should be easy?
 

Thanks for the reply. Unfortunately I need something more advanced. I
actually have 20 tiers to check against and I found that an if
statement can't be nested on so many levels. In addition, the list can
be up to 50,000 rows.

Tiering Macro, Should be easy?
 

In article . com,
says...
Thanks for the reply. Unfortunately I need something more advanced. I
actually have 20 tiers to check against and I found that an if
statement can't be nested on so many levels. In addition, the list can
be up to 50,000 rows.


As long as you ask questions that you don't really want answered all
you will succeed in doing is wasting everybody's time.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions