An alternative to using a loop to speed up code

Question

I have 2 questions, the first one concerns the excel formula, which I cannot replicate in VBA, although I used the write macro to extract the formula, the second because I could not solve my first question about the effectiveness of the code: Basically in the cell AR2 I put the excel formula:

IF=(P2="LDN";"UK;IF(P2="MAD";"SPAIN"
IF(P2="PRA";"CZECH REPUBLIC";"")))))))))

I do this for a group of countries.

and then I do an autocomplete assignment in my second row to the last row with the data in my worksheet to get the result. The main problem is that it works well in excel, but when coding in VBA using a vba recorder, I have an error in the code below. although I just copied the paste of the result of the vba recorder. Please find the code below.

 i = Range("A:A").Find("*", [A1], xlValues, xlWhole, xlByRows, xlPrevious).Row

    Range("AR2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-28]=""LDN"",""UK"",IF(RC[-28]=""MAD"",""SPAIN"",IF(RC[-28]=""STO"",""SWEDEN"",IF(RC[-28]=""DUB"",""IRELAND"",IF(RC[-28]=""SAO"",""BRASIL"",IF(RC[-28]=""PAR"",""FRANCE"",IF(RC[-28]=""TOR"",""CANADA"",IF(RC[-28]=""TOK"",""JAPAN"",IF(RC[-28]=""ZUR"",""SWITZERLAND"",IF(RC[-28]=""HKG"",""HONG KONG"",IF(RC[-28]=""HEL"",""FINLAND"",IF(RC[-28]=""MIL"",""ITALY"",IF(R"& _
    ""FRA"",""GERMANY"",IF(RC[-28]=""COP"",""DANEMARK"",IF(RC[-28]=""BRU"",""BELGIUM"",IF(RC[-28]=""AMS"",""NETHERLANDS"",IF(RC[-28]=""SIN"",""SINGAPORE"",IF(RC[-28]=""SEO"",""SOUTH KOREA"",IF(RC[-28]=""OSL"",""NORWAY"",IF(RC[-28]=""LIS"",""PORTUGAL"",IF(RC[-28]=""NYK"",""USA"",IF(RC[-28]=""VIE"",""AUSTRIA"",IF(RC[-28]=""LUX"",""LUXEMBOURG"",IF(RC[-28]=""JOH"",""SOUTH AF"& _
    "(RC[-28]=""MEX"",""MEXICO"",IF(RC[-28]=""SYD"",""AUSTRALIA"",IF(RC[-28]=""TAI"",""TAIWAN"",IF(RC[-28]=""VAR"",""POLAND"",IF(RC[-28]=""BUD"",""HUNGARY"",IF(RC[-28]=""IST"",""TURKEY"",IF(RC[-28]=""BAN"",""INDIA"",IF(RC[-28]=""MOS"",""RUSSIA"",IF(RC[-28]=""TEL"",""ISRAEL"",IF(RC[-28]=""KUA"",""MALAYSIA"",IF(RC[-28]=""ATH"",""GREECE"",IF(RC[-28]     =""PRA"",""CZECH REPUBLIC"& _
    "))))))))))))))))))))))))))))))))))"
Range("AR2").Select
Selection.AutoFill Destination:=Range("AR2:AR" & i)

, vba, Loop, , , 20k .... 5min insteand excel: :

For j = 2 To i

If Range("P" & j) = "AMS" Then Range("AR" & j) = "NETHERLANDS"
If Range("P" & j) = "ATH" Then Range("AR" & j) = "GREECE"
If Range("P" & j) = "BAN" Then Range("AR" & j) = "INDIA"
If Range("P" & j) = "BRU" Then Range("AR" & j) = "BELGIUM"
If Range("P" & j) = "BUD" Then Range("AR" & j) = "HUNGARY"
If Range("P" & j) = "COP" Then Range("AR" & j) = "DANEMARK"
.
.
.
.
.

If Range("P" & j) = "VAR" Then Range("AR" & j) = "POLAND"
If Range("P" & j) = "VIE" Then Range("AR" & j) = "AUSTRIA"
If Range("P" & j) = "ZUR" Then Range("AR" & j) = "SWITZERLAND"


Next j

excel formulat VBA, , 20 . , excel .

Answer 1

, :

Dim vals() As Variant
vals = Range("P1:P1000")  ' Substitute with the range you're looking at

Dim i As Integer
Dim val As String

For i = LBound(vals, 1) To UBound(vals, 1)
    val = CStr(vals(i, 1))

    Select Case val
        Case "AMS"
            Range("AR" & i + some_fixed_offset_if_needed) = "NETHERLANDS"
        Case "AR":
            ' .....
        ' Case .....
    End Select
Next i

, , . . . - , , .

-, , If Statementments - Use ... ElseIf...

... , , . .

, . , Scripting.Dictionary (, "" ) . .

, WorksheetFormula.something VBA.

Answer 2

VBA, ; VBA .

-, , (.. VBA) . , .

Excel ( - , ). , A, , , , , B2:

=INDEX($F$4:$F$12,MATCH(A2,$E$4:$E$12,0))

.

VBA, ( ) :

Dim i As Long
Dim cityCodes
Dim countryNames
Dim listOfCountryNames
Dim listOfCityCodes

'Read look-up table from sheet.
listOfCityCodes = WorksheetFunction.Transpose(Range("E4:E12").Value)
listOfCountryNames = WorksheetFunction.Transpose(Range("F4:F12").Value)

'Read the input city codes from sheet to array
cityCodes = Range("A2:A9").Value
'Make a blank array of same size to receive the corresponding country names
ReDim countryNames(LBound(cityCodes, 1) To UBound(cityCodes, 1), _
    LBound(cityCodes, 2) To UBound(cityCodes, 2))

'Lookup individual country names one by one, write them in array
For i = LBound(cityCodes, 1) To UBound(cityCodes, 1)
    countryNames(i, 1) = listOfCountryNames( _
        WorksheetFunction.Match(cityCodes(i, 1), listOfCityCodes, 0))
Next i

'Write country names from array to sheet
Range("B2:B9").Value = countryNames

, , , , , . , / .