I have a cell say A2 that has a number in it .... I would like to return a
number value in another cell. I can write an if then else statement: if(a2 =
5 then,1,if(a2=10,2,if(a2=15,3))) etc. but this gets long (i have 30-40
parameters) I know there has to be a way to write it in VB so that I could
just go down a list (if I need to change the value later) but for some reason
I am having a problem with the language. I would appreciate any help!
--
Jackie

Is your example true to life, or just an example? If it's true to
life, then instead of code you might use just a formula in the other
cell:
=A2/5

Or, you might insert a new tab that holds lookup values, and on the
original sheet use VLOOKUP formulas to return a value corresponding to
the value in A2. Might these ideas work for you?

No need to use VB.

One way, based on your example:

=IF(MOD(A2,5)=0,INT(A2/5),"")

but to be more flexible, use a VLOOKUP:

In another part of your workbook, perhaps a second sheet enter your
equivalents:

A B
1 5 1
2 10 2
3 15 3
....

then in your main sheet enter

=VLOOKUP(A2, Sheet2!A:B, 2, FALSE)

the fourth parameter can be true if you want an approximate match.


In article ,
Jackie wrote:

I have a cell say A2 that has a number in it .... I would like to return a
number value in another cell. I can write an if then else statement: if(a2 =
5 then,1,if(a2=10,2,if(a2=15,3))) etc. but this gets long (i have 30-40
parameters) I know there has to be a way to write it in VB so that I could
just go down a list (if I need to change the value later) but for some reason
I am having a problem with the language. I would appreciate any help!

Hi Jackie,

You can use Select Case statement:

Select Case Range("A2")
Case 5
Range("A3").Value = 1
Case 10
Range("A3").Value = 2
Case 15
Range("A3").Value = 3
....
End Select

--
Best regards,
Peter Jaušovec
http://blog.jausovec.net
http://office.jausovec.net


"Jackie" je napisal:

I have a cell say A2 that has a number in it .... I would like to return a
number value in another cell. I can write an if then else statement: if(a2 =
5 then,1,if(a2=10,2,if(a2=15,3))) etc. but this gets long (i have 30-40
parameters) I know there has to be a way to write it in VB so that I could
just go down a list (if I need to change the value later) but for some reason
I am having a problem with the language. I would appreciate any help!
--
Jackie