title: Alternative to using loop to make code run faster
body: <p>I have 2 questions, the first one is about an excel formula that i can't replicate in VBA even though i've used the record macro to retrieve the formula, the second comes because i couldnt solve my first question and is about code efficiency:
  Basically in the cell AR2 i'm putting an excel formula : </p>
  
  <pre><code>IF=(P2="LDN";"UK;IF(P2="MAD";"SPAIN"
  IF(P2="PRA";"CZECH REPUBLIC";"")))))))))
  </code></pre>
  
  <p>i m doing this for a bunch of countries.</p>
  
  <p>and then i m doing the autfill cell destination on my second row till the last row with data on my sheet to get the result.
  The main issue is that in excel it works well however when coding in VBA with vba recorder i have an error in the below code. although i just copy pasted the result of the vba recorder.
  Please find the code below.</p>
  
  <pre><code> 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"&amp; _
      ""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"&amp; _
      "(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"&amp; _
      "))))))))))))))))))))))))))))))))))"
  Range("AR2").Select
  Selection.AutoFill Destination:=Range("AR2:AR" &amp; i)
  </code></pre>
  
  <p>As the above code didnt worked i tried to do it in vba using Loop however i found out that it takes ages to get the result as i have almost 20k rows.... 5min to process insteand of instant result with excel formula :
  The code of my loop is here :</p>
  
  <pre><code>For j = 2 To i
  
  If Range("P" &amp; j) = "AMS" Then Range("AR" &amp; j) = "NETHERLANDS"
  If Range("P" &amp; j) = "ATH" Then Range("AR" &amp; j) = "GREECE"
  If Range("P" &amp; j) = "BAN" Then Range("AR" &amp; j) = "INDIA"
  If Range("P" &amp; j) = "BRU" Then Range("AR" &amp; j) = "BELGIUM"
  If Range("P" &amp; j) = "BUD" Then Range("AR" &amp; j) = "HUNGARY"
  If Range("P" &amp; j) = "COP" Then Range("AR" &amp; j) = "DANEMARK"
  .
  .
  .
  .
  .
  
  If Range("P" &amp; j) = "VAR" Then Range("AR" &amp; j) = "POLAND"
  If Range("P" &amp; j) = "VIE" Then Range("AR" &amp; j) = "AUSTRIA"
  If Range("P" &amp; j) = "ZUR" Then Range("AR" &amp; j) = "SWITZERLAND"
  
  
  Next j
  </code></pre>
  
  <p>If the excel formulat doesnt work in VBA, how can i code in an efficient and fast way in order to get the countries for at least 20k rows without having to wait and get the result almost instantanely like for the excel formula auto fill mode.</p>
  
  <p>Many thanks fo ryour help
  Olivier</p>