Excel 2013 keep crashing when i run macro

you are using FormulaR1C1 method
i assume because you just recorded marco and used whatever it gave you and tried to amend it
thats ok....its how i learnt also

R = Row
C = Column
R(0)C(-3) = 0 rows, 3 columns back..relative to your range

take this line of code
and assuming rownum is 30

Range("P" & RowNum).FormulaR1C1 = "= VLOOKUP(RC[-3],[MISSINGUSERNAMEDEPT.xlsx]Sheet1!C1:C5,2,0)"

range would (P30)
RC[-3] = p30 + zero rows + (- 3 columns) = M30
explained from above C1:C5 = A:E

the formula now reads
vlookup(m30,[Workbook]Worksheet!A:E,2,0)

more reading about it can be found here
https://excelmate.wordpress.com/2013...e-style-vs-a1/

alternatively don't use R1C1 just use .formula instead

Range("P" & rownum).Formula = "= VLOOKUP(" & Range("M" & rownum).Address & ",[MISSINGUSERNAMEDEPT.xlsx]Sheet1!A:E,2,0)"

if you want it part of the code

    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
        
        If UCase(Cells(rownum, 16).Value) = "#N/A" Then
            Cells(rownum, 16).Value = "NOT INDICATED"
        End If
        
        If UCase(Cells(rownum, 17).Value) = "#N/A" Then
            Cells(rownum, 17).Value = "NOT INDICATED"
        End If
        
    Next

add two more IF blocks

Originally Posted by humdingaling

if you want it part of the code

    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
        
        If UCase(Cells(rownum, 16).Value) = "#N/A" Then
            Cells(rownum, 16).Value = "NOT INDICATED"
        End If
        
        If UCase(Cells(rownum, 17).Value) = "#N/A" Then
            Cells(rownum, 17).Value = "NOT INDICATED"
        End If
        
    Next

add two more IF blocks

Normally , i do this manually

Ctrl+H
Find "#n/a"
Replace with "NOT INDICATED"

and I want to automate the process cos I'm adding more methods after this Method .

Anyway, Thank you so much . I really really appreciate your help and your quick response. Thank you