Streamline My Script

[Jaye7]

I use the following script a lot and get hassled for using it as it contains select etc...

Can anyone help streamline it so that it is more efficient.

Sub Test3()
Application.ScreenUpdating = False
Application.Calculation = xlManual
Columns("O:O").Select
    Selection.NumberFormat = "General"
 
    Range("O2").Select
 
' want better way to select this column range using column to the left range
 
    ActiveCell.Offset(0, -1).Select
Cells(100000, ActiveCell.Column).Select
   Range(Selection, Selection.End(xlUp)).Select
   ActiveCell.Select
   ActiveCell.Offset(0, 1).Select
Set BottomCell = ActiveCell
    Set TopCell = Cells(2, ActiveCell.Column)
      Range(TopCell, BottomCell).Select
 
 
' want better way to fill selection
 
    Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula = False Then
End If
 
 ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-14],C[1], 1, FALSE)"
ActiveCell.Offset(1, 0).Select
 
Next
 
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

 

[AlphaFrog]

Sub Test3()

    Columns("O:O").NumberFormat = "General"
    Range("O2", Range("N" & Rows.Count).End(xlUp).Offset(, 1)).FormulaR1C1 = "=VLOOKUP(RC[-14],C[1], 1, FALSE)"
    
End Sub

 

[Jaye7]

Wow!! That is great and super fast working on over 85000 lines.

Thanks heaps Alphafrog, it will come in very handy.