I can't run my macro well. I have been searching for the problem
but it seems that my vlookup has a problem and I cant figure it out .

My Patient Merge have a lot of data . I think thats what my excel run slow but i have use -"With Application" to run.

Using vlookup manually ,
Lookup_value: Column C in Book1 Workbook
Table_Array:Column J in Patient Merge Workbook
Col_Index_Num: 1
Range_lookup:0




Private Sub Unsuccessful()
'Update Column S and T
'S = Active Ext ID , T = Inactive Ext ID
'No headers for Column S and T 

Dim MaxRowNum As Long

With Application
        .ScreenUpdating = False
        .Calculation = xlManual
End With

Sheets("Book1").Select

'Find the max number of row
MaxRowNum = 1
        
    Do While Cells(MaxRowNum, 2) <> "" Or Cells(MaxRowNum + 1, 2) <> ""
        MaxRowNum = MaxRowNum + 1
    Loop

'Vlookup Active Ext ID frm PATIENT MERGE file
Range("S2").FormulaR1C1 = "= VLOOKUP(RC[-16],[PatientMerge.xls]2015!R1:R1048576,1,0)"

'Vlookup Inactive Ext ID
Range("T2").FormulaR1C1 = "= VLOOKUP(RC[-17],[PatientMerge.xls]2015!R1:R1048576,1,0)"

    
With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        
    End With

    'AutoFill formula. Copy and paste data as value
      Range("S2:T2").Select
    Selection.AutoFill Destination:=Range("S2:T" & MaxRowNum), Type:=xlFillDefault
    
    Columns("S:T").Select
    Columns("S:T").EntireColumn.AutoFit
    
    Columns("S:T").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub
Kindly help . I really appreciate your help. I tried a few methods of using vlookup but no avail