Excel 2013 keep crashing when i run macro

Originally Posted by humdingaling

This is example of what it could look like
i dont know what your sheet name is called for your vlookup
edit sheet1 to whatever your sheet name is on the MISSINGUSERNAMEDEPT file

i also assumed C1:C5 for your vlookup as i dont know which column your starting from or ending
change this to suit your needs
C1:C5 = A:E

Private Sub Missing_UserName_Dept()

    ' Update User Name and Dept from Missing User name & dept xlsx file

    Dim MaxRowNum, RowNum As Long
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlManual
        
    End With
    
    Sheets("Simpat").Select
    'Counting number of rows / Find the max number of row
    MaxRowNum = 1
        
    Do While Cells(MaxRowNum, 2) <> "" Or Cells(MaxRowNum + 1, 2) <> ""
        MaxRowNum = MaxRowNum + 1
    Loop
    
    'Find the row with the USERNAME & DEPT - "NOT INDICATED" and use a vlookup Function

    RowNum = 1
    
    For RowNum = 1 To MaxRowNum
    
        If UCase(Cells(RowNum, 16)) Like "*NOT INDICATED*" Or UCase(Cells(RowNum, 17)) Like "*NOT INDICATED*" Then
        
            'Vlookup User Name from the file MISSINGUSERNAMEDEPT.xlsx
            Range("P" & RowNum).FormulaR1C1 = "= VLOOKUP(RC[-3],[MISSINGUSERNAMEDEPT.xlsx]Sheet1!C1:C5,2,0)"
            
            'Vlookup Dept from the file MISSINGUSERNAMEDEPT.xlsx
            
            Range("Q" & RowNum).FormulaR1C1 = "= VLOOKUP(RC[-4],[MISSINGUSERNAMEDEPT.xlsx]Sheet1!C1:C5,3,0)"
        
            Range(Cells(RowNum, "P"), Cells(RowNum, "Q")).Select
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues
            
        
        End If
    Next
    
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        
    End With
    
End Sub

i assume your maxrownum is working correctly....

Yeah , the maxrownum is working correctly .
Thank you so much It can work.
Could i ask one more thing , Is it possible to replace "#N/A" to "NOT INDICATED" ?