If..statement

  • Thread starter Thread starter amatuer
  • Start date Start date
A

amatuer

Hi, could sumone pls help me with a nested if statement...
I want to assign a number to a cell depending on wat the contents of
another cell is.

eg: 100 - 104 assign 40
105-109 assign 44
110-114 assign 47
115+ assign 50

this is 1 if statement, id like to also add anothr nested if statement
to that same cell...

any suggestion? Thanx in advance
 
Try using a case statement:


Sub Test()
Select Case Range("A1").Value
Case 100 To 104
Range("B1").Value = 40
Case 105 To 109
Range("B1").Value = 44
Case 110 To 114
Range("B1").Value = 47
Case Is >= 115
Range("B1").Value = 50
End Select
End Sub
 
Hi Sandy, could you expand a little for me please. Where exactly whould
i put the sub? And hw should i call the sub.
Thanx sooo much.
 
If you weren't looking for a macro you could use this if function in a
cell(change A1 to what ever reference you'd like):

=IF(AND(A1>=100,A1<=104),40,IF(AND(A1>=105,A1<=109),44,IF(AND(A1>=110,A1<=114),47,IF(A1>=115,50,"Nothing"))))

If you want to use the macro:

open VBE (Alt +F11)
Click insert ==> Module paste the formula in the window.
To run click Tools ==> macro ==> marcos and highlight "Test" and click
play.

You'll need to change the ranges to the cell you want to change. If
you're not that well versed in VBA code I would recommend using the
formula instead

Sandy
 
Thnx alot, i will try it out.
If you weren't looking for a macro you could use this if function in a
cell(change A1 to what ever reference you'd like):

=IF(AND(A1>=100,A1<=104),40,IF(AND(A1>=105,A1<=109),44,IF(AND(A1>=110,A1<=114),47,IF(A1>=115,50,"Nothing"))))

If you want to use the macro:

open VBE (Alt +F11)
Click insert ==> Module paste the formula in the window.
To run click Tools ==> macro ==> marcos and highlight "Test" and click
play.

You'll need to change the ranges to the cell you want to change. If
you're not that well versed in VBA code I would recommend using the
formula instead

Sandy
Click to expand...
 
Back
Top