A Simple Computer Library

Convert select case in Visual Basic code to simple formula in Microsoft Excel

In this exercise, the value selection is from 1 to 3 only. BTW, you can make the last number of value higher (more than 3).
Before continuing this subject, I show a simple case in Visual Basic form example.
Private Sub Command1_Click()
Dim Total As Integer
Text0.SetFocus
Select Case Text0.Text
Case 1
Total = 5 * 10
Case 2
Total = 6 * 100
Case 3
Total = 7 * 1000
End Select
Text2.SetFocus
Text2.Text = Total
End Sub

Here is the conversion version in Microsoft Excel formula.
I convert the Text0.Text to cell A6 and the Text2.Text to cell C6.
In this case, I consider cell C6 as a result of the calculation and the formula is:
=IF(A6>3,”",CHOOSE(A6,5*10,6*100,7*1000))
Note:
If the cell A6 is empty, the result will be #VALUE!
The valid number in cell A6 is 1, 2 and 3 only.

This entry was posted on Sunday, September 23rd, 2007 at 2:57 pm and is filed under Microsoft Excel. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.