IF Statement
Posted by Julie on February 13, 2001 11:39 AM
I need a macro or method that does the following:
If C14 = "A" then C16 = value1a and C18 = value1b
If C14 = "B" then C16 = value2a and C18 = value2b
If C14 = "C" then C16 = value3a and C18 = value3b
and so on....
I have 14 conditions each of which have a differnet
set of values (a & b).
thanks!
Posted by Aladin Akyurek on February 13, 2001 11:51 AM
It seems you need first a table letters ("A","B", etc) and associated values. Set up this table on some sheet across 3 columns as follows:
A val1a val1b
B val2a val2b
C val3a val3b
. . .
. . .
Select the cells of this table and name it, e.g., TABLE, via the Name Box (or via Insert,Define,Name).
Type in
C16 =VLOOKUP(C14,TABLE,2,0) [0 for FALSE]
C18 =VLOOKUP(C14,TABLE,3,0)
This would do what you want.
Aladin
.
Posted by KeeKee on February 13, 2001 11:58 AM
I wuld use a do loop that reads the first cell, does the appropriate function, then moves to the next cell:
(this reads A14, does 1 of 4 things (0-3), then stops when the value is 4.
Sub do_loop()
Range("a14").Select
Do Until ActiveCell = 4
If ActiveCell = 0 Then
Call area_border
ActiveCell.Offset(0, 1).Range("A1").Select
ElseIf ActiveCell = 1 Then
Call area_sum
ActiveCell.Offset(0, 1).Range("A1").Select
ElseIf ActiveCell = 2 Then
Call line_sum
ActiveCell.Offset(0, 1).Range("A1").Select
ElseIf ActiveCell = 3 Then
Call area_sum
ActiveCell.Offset(0, 1).Range("A1").Select
End If
Loop
End Sub
Hope this helps
Posted by Mark W. on February 13, 2001 1:10 PM
Enter the formula,
=VLOOKUP($C$14,{"A",value1a;"B",value2a;"C",value3a},2,0),
into cell C16 and the formula,
=VLOOKUP($C$14,{"A",value1b;"B",value2b;"C",value3b},2,0),
into cell C18 where value1a, value1b, etc. are the
appropriate numeric values.
Posted by Aladin Akyurek on February 13, 2001 1:42 PM
Mark: Next time I'll provide also the array-using version of vlookup along the ordinary version, with the following comment added: If the values of the lookup table will never change, the array version is the best choice, otherwise the ordinary version of vlookup would be the way to go. I'm sure you'd agree.
Aladin
Posted by Michelle on February 13, 2001 2:19 PM
I think it would be easier to do a select case macro:
Select Case
Case 1:
Result
Case 2:
Result
Case 3:
Result
End Select
Posted by Aladin Akyurek on February 13, 2001 2:57 PM
It seems to me that a beginner or even an intermediate level user of spreadsheets would be better off taking VLOOKUPs that have been suggested.
Cheers.
Aladin
Posted by Mark W. on February 13, 2001 4:02 PM
Aladin, even if the values were to change I'd still
use an array constant. But, instead of coding the
array constant itself as the 2nd argument to the
VLOOKUP() function, I'd define a name (e.g., tax_table,
grade_scale) that refers to the array constant (e.g.,
={"F",0;"D",60...}) and then use the name. This
practice makes the VLOOKUP() more readable
(comprehensible) and allows one to make a global
change to the table values.
