Brtdku

Following macro get input (Ticker) and translate it into Countries Name. After getting input it go through all If conditions. how could I stop macro from going through all Ifs when condition met at first If?

For example: Macro get input "BU" after first If variable UT becomes "BULGARIA" NOW I need to stop here and jump at the end of last If. how it is done? Is there any other way to tackle this kind of situation?

ST:

T = InputBox("Country Ticker?", "Finding country name using a Ticker", "Write Country Name to Transfer Data")



ut = UCase(T)

'Dim C As Integer

C = Len(T)



If T = "Write Country Name to Transfer Data" Then

MsgBox "No Country Name Input"

GoTo ST



ElseIf ut = "" Then

Exit Sub



ElseIf C < 2 Then

MsgBox "Wrong Country Name :) Please Try Again!"

GoTo ST

End If



If ut = "BU" Then ut = "BULGARIA"

If ut = "AR" Then ut = "ARGENTINA"

If ut = "CI" Then ut = "CHILE"

If ut = "CB" Then ut = "COLOMBIA"

If ut = "CZ" Then ut = "CROATIA"

If ut = "CP" Then ut = "CZECH REPUBLIC"

If ut = "ET" Then ut = "ESTONIA"

If ut = "HB" Then ut = "HUNGARY"

If ut = "IQ" Then ut = "IRAQ"

If ut = "KN" Then ut = "KENYA"

If ut = "LR" Then ut = "LATVIA"

If ut = "LH" Then ut = "LITHUANIA"

If ut = "MM" Then ut = "MEXICO"

If ut = "NL" Then ut = "NIGERIA"

If ut = "PW" Then ut = "POLAND"

If ut = "RO" Then ut = "ROMANIA"

If ut = "RM" Then ut = "RUSSIA"

If ut = "RU" Then ut = "RUSSIA"

If ut = "RX" Then ut = "RUSSIA"

If ut = "SJ" Then ut = "SOUTH AFRICA"

If ut = "TI" Then ut = "TURKEY"

If ut = "UG" Then ut = "UGANDA"

If ut = "UZ" Then ut = "UKRAINE"

If ut = "ZH" Then ut = "ZIMBABWE"

If ut = "AB" Then ut = "SAUDI ARABIA"

If ut = "EY" Then ut = "EGYPT"

If ut = "OM" Then ut = "OMAN"

If ut = "QD" Then ut = "QATAR"

If ut = "UH" Then ut = "UNITED ARAB EMIRATES"

If ut = "DU" Then ut = "UNITED ARAB EMIRATES"

If ut = "KK" Then ut = "KUWAIT"

If ut = "BI" Then ut = "BAHRAIN"

If ut = "JR" Then ut = "JORDAN"

If ut = "MC" Then ut = "MOROCCO"

If ut = "TZ" Then ut = "TANZANIA"

If ut = "RW" Then ut = "RWANDA"

If ut = "CD" Then ut = "COTE D IVOIRE"

If ut = "ZL" Then ut = "Zambia"

If ut = "SG" Then ut = "Serbia"

If ut = "NW" Then ut = "NAMIBIA"

If ut = "GN" Then ut = "GHANA"

If ut = "TP" Then ut = "TRINIDAD & TOBAGO"

If ut = "GA" Then ut = "GREECE"

If ut = "PS" Then ut = "PALESTINE"

If ut = "NO" Then ut = "NOTABLE RESEARCH"

C = Len(ut)

If C < 3 Then Exit Sub

Use Select Case

Select Case ut

    Case "DU","UH"

        ut = "UNITED ARAB EMIRATES"

    Case "BU"

        ut = "BULGARIA"

    Case "AR"

        ut = "ARGENTINA"

        '...

End Select

You might also consider using a dictionary where the abbr e.g. BU is the key and the replacement is the value.

Example with dictionary:

Dim dict As Object, ut As String

Set dict = CreateObject("Scripting.Dictionary")

dict.Add "BU", "Bulagaria"

dict.Add "AR", "Argentina"

'etc



'Example

ut = "AR"

If dict.Exists(ut) Then ut = dict(ut)

After the first If, all the others should be ElseIf. The ‘Else’ part of that causes it to be skipped if a prior If test was true.

This question made me consider which solution would perform faster between Select Case and ElseIf (I previously did a comparison of Long vs Integer). I ran some tests of a bunch of Select Case code and compared to similar ElseIf code.

Predictably there wasn't much of a difference, but Select statements always ran slightly slower than ElseIf. I did a few iterations of about 6 billion case checks and the difference seemed consistent.

I doubt this delta of a few seconds on such a massive volume is ever worth deviating from whatever approach you're most comfortable with. However here are my results and the code I used if anyone is curious:

'Module variables

Dim beginTIme As Double, i As Long, r As Long

Const MaxValue As Long = 999999999



Sub goSelect()

    beginTIme = Now



    For i = 0 To MaxValue

        r = r Mod 12



        Select Case r

            Case 0

            Case 1

            Case 2

            Case 3

            Case 4

            Case 5

            Case 6

            Case 7

            Case 8

            Case 9

            Case 0

            Case 11

        End Select



    Next i



    With Cells(Rows.Count, 1).End(xlUp)

        .Offset(1, 0).Value = Round((Now - beginTIme) * 3600 * 24, 0) & " seconds using select."

        .Offset(1, 1).Value = MaxValue

    End With



End Sub





Sub go4If()

    beginTIme = Now



    For i = 0 To MaxValue

        r = r Mod 12



        If r = 0 Then

            ElseIf r = 1 Then

            ElseIf r = 2 Then

            ElseIf r = 3 Then

            ElseIf r = 4 Then

            ElseIf r = 5 Then

            ElseIf r = 6 Then

            ElseIf r = 7 Then

            ElseIf r = 8 Then

            ElseIf r = 9 Then

            ElseIf r = 10 Then

            ElseIf r = 11 Then

        End If



    Next i



    With Cells(Rows.Count, 3).End(xlUp)

        .Offset(1, 0).Value = Round((Now - beginTIme) * 3600 * 24, 0) & " seconds using elseif."

        .Offset(1, 1).Value = MaxValue

    End With



End Sub

'Yeah this is what I'm doing on Saturday night....🎉

You could use a Case statement:

Select Case ut

Case "BU"

    ut = "BULGARIA"

Case "AR"

    ut = "ARGENTINA"

...

End Select

You could also use if else:

If ut = "BU" Then

    ut = "BULGARIA"

ElseIf ut = "AR" Then

    ut = "ARGENTINA"

ElseIf

    ...

End If

You either can use the:

If ut = "BU" Then

   ut = "Bulgaria"

Elseif ut ="ZL" Then

   ut = "Zambia"

...

End If

But with so much conditions I would recommed the Select statement. This way you need to write the varible just once, and you have a Case Else when no condition is met:

Select Case ut

   Case "ZL"

       ut = "Zambia"

   Case "BU"

        ...

   ....

   Case Else

       Msgbox "Country not found"

End Select