# I need help with a Excel macro to create a column with information based on another column

Column A has the number of gallons that blood donors have given.  They range anywhere from 1-100. I would like a macro that takes that number, inserts a new column A and returns a value.  The value will be based on a number range. The returned value will be Bronze, Silver, Gold or Platinum.

1 thru 4      Bronze
5 thru 9      Silver
10 thru 14      Gold
15 thru 100       Platinum

Hope this makes sense.
gallon-certs-test.xls
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Hi,

pls try

``````Sub Macro1()
Columns("A:A").Insert Shift:=xlToRight
Range("A1").FormulaR1C1 = "Rank"
Range("A1").Font.Bold = True
For Each c In Range(Range("B2"), Range("B2").End(xlDown)).Offset(, -1)
Select Case c.Offset(, 1)
Case 1 To 4
c.Value = "Bronze"
Case 5 To 9
c.Value = "Silver"
Case 10 To 14
c.Value = "Gold"
Case 15 To 100
c.Value = "Platinum"
Case Else
c.Value = ""
End Select
Next

End Sub
``````
Regards

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Volunteer Chief Information OfficerCommented:
if you want to avoid macros altogether, you can do this:

=IF(AND(B1>=1,B1<=4),"Bronze",IF(AND(B1>=5,B1<=9),"Silver",IF(AND(B1>=10,B1<=19),"Gold","Platinum")))