VBA Faster Code

[ziad alsayed]

dear all

the below code is looping over 20000 row , and it is taking a bit of time .
please advise how can i make it faster

appreciate any help

For i = 2 To finalrow
Cells(i, 4).FormulaR1C1 = "=LEFT(RC[-1],1)"
Cells(i, 5).FormulaR1C1 = "=IF(RC[-1]=""1"",""PHC"",IF(RC[-1]=""2"",""Apapa"",IF(RC[-1]=""3"",""VI"",IF(RC[-1]=""4"",""Kano"",IF(RC[-1]=""5"",""Abuja"",IF(RC[-1]=""6"",""Ikeja"",""""))))))"
Next i

 

[MrKowz]

Make sure you are using Application.ScreenUpdating = False

Also, since you are putting in formulas, consider turning off calculations as well and then turning the calculations back on after it is done putting them in:

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
For i = 2 To finalrow
    Cells(i, 4).FormulaR1C1 = "=LEFT(RC[-1],1)"
    Cells(i, 5).FormulaR1C1 = "=IF(RC[-1]=""1"",""PHC"",IF(RC[-1]=""2"",""Apapa"",IF(RC[-1]=""3"",""VI"",IF(RC[-1]=""4"",""Kano"",IF(RC[-1]=""5"",""Abuja"",IF(RC[-1]=""6"",""Ikeja"",""""))))))"
Next i
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

 

[VoG]

Try

Application.Calculation = xlCalculationManual
For i = 2 To finalrow
    Cells(i, 4).FormulaR1C1 = "=LEFT(RC[-1],1)"
    Cells(i, 5).FormulaR1C1 = "=IF(RC[-1]=""1"",""PHC"",IF(RC[-1]=""2"",""Apapa"",IF(RC[-1]=""3"",""VI"",IF(RC[-1]=""4"",""Kano"",IF(RC[-1]=""5"",""Abuja"",IF(RC[-1]=""6"",""Ikeja"",""""))))))"
Next i
Application.Calculation = xlCalculationAutomatic
Calculate

 

[ziad alsayed]

dear MrKowz , it works perfectly.
thanks.

 

[texasalynn]

try this

Sub add_formula()
Dim LR As Long
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Cells(2, 4).FormulaR1C1 = "=LEFT(RC[-1],1)"
Cells(2, 5).FormulaR1C1 = "=IF(RC[-1]=""1"",""PHC"",IF(RC[-1]=""2"",""Apapa"",IF(RC[-1]=""3"",""VI"",IF(RC[-1]=""4"",""Kano"",IF(RC[-1]=""5"",""Abuja"",IF(RC[-1]=""6"",""Ikeja"",""""))))))"
Range("D2").AutoFill Destination:=Range("D2:D" & LR)
Range("E2").AutoFill Destination:=Range("E2:E" & LR)
End Sub

 

[JackDanIce]

I'd create a table with of 6 rows by 2 columns, first column with numbers 1-6 and second columns with the associated values. Then use a VLOOKUP to reference and return what you want.

This may also be faster, but I've used absolute references, rather than relative:

Application.ScreenUpdating = False
With Range("D2:D" & finalrow)
  .Formula = "=VLOOKUP(LEFT(C2,1),$E$1:$F$6,2,0)"
  .FillDown
End With
Application.ScreenUpdating = True

Red bit is range of where ever your table is

 

[ziad alsayed]

dear texasalynn

it works fine and it is really fast, less than a second

thanks.