I am using a large number of ElseIf statements, and running into
problems. I was informed to use Select Case instead. However I am
not familiar with the Select Case syntax.

I have written below a small sample of my ElseIf statement, can
someone please write the equivalent in Select Case syntax.

Thank you in advance,

Susan




Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If [b6] = "United Kingdom" And [b62] = "USD" Then
[b16] = [r13]
[b17] = [a1] * [b1]
ElseIf [b6] = "United Kingdom" And [b62] = "CAD" Then
[b16] = [p13]
[b17] = [a2] * [b2]
ElseIf [b6] = "Hong Kong" And [b62] = "USD" Then
[b16] = [r2]
[b17] = [a6] * [b6]
ElseIf [b6] = "Hong Kong" And [b62] = "CAD" Then
[b16] = [p2]
[b17] = [a7] * [b7]
Else:
[b16] = ""
[b17] = ""
End If

End Sub

From help
Using Select Case Statements
Use the Select Case statement as an alternative to using ElseIf in
If...Then...Else statements when comparing one expression to several
different values. While If...Then...Else statements can evaluate a different
expression for each ElseIf statement, the Select Case statement evaluates an
expression only once, at the top of the control structure.

In the following example, the Select Case statement evaluates the
performance argument that is passed to the procedure. Note that each Case
statement can contain more than one value, a range of values, or a
combination of values and comparison operators. The optional Case Else
statement runs if the Select Case statement doesn't match a value in any of
the Case statements.

Function Bonus(performance, salary)
Select Case performance
Case 1
Bonus = salary * 0.1
Case 2, 3
Bonus = salary * 0.09
Case 4 To 6
Bonus = salary * 0.07
Case Is 8
Bonus = 100
Case Else
Bonus = 0
End Select
End Function

--
Don Guillett
SalesAid Software

"Susan Hayes" wrote in message
...
I am using a large number of ElseIf statements, and running into
problems. I was informed to use Select Case instead. However I am
not familiar with the Select Case syntax.

I have written below a small sample of my ElseIf statement, can
someone please write the equivalent in Select Case syntax.

Thank you in advance,

Susan




Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If [b6] = "United Kingdom" And [b62] = "USD" Then
[b16] = [r13]
[b17] = [a1] * [b1]
ElseIf [b6] = "United Kingdom" And [b62] = "CAD" Then
[b16] = [p13]
[b17] = [a2] * [b2]
ElseIf [b6] = "Hong Kong" And [b62] = "USD" Then
[b16] = [r2]
[b17] = [a6] * [b6]
ElseIf [b6] = "Hong Kong" And [b62] = "CAD" Then
[b16] = [p2]
[b17] = [a7] * [b7]
Else:
[b16] = ""
[b17] = ""
End If

End Sub