Excel 2013 keep crashing when i run macro

My excel 2013 keep crashing whenever i run this macro .
Is there something wrong with my code that crashes my excel .
Please help ,thank you

Private Sub Missing_UserName_Dept()

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

Dim MaxRowNum, RowNum As Integer

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
Do While RowNum < 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("P3").FormulaR1C1 = "= VLOOKUP(C[-3],MISSINGUSERNAMEDEPT.xlsx!R1:R1048576,2,0)"

'Vlookup Dept from the file MISSINGUSERNAMEDEPT.xlsx

Range("Q3").FormulaR1C1 = "= VLOOKUP(C[-4],MISSINGUSERNAMEDEPT.xlsx!R1:R1048576,3,0)"

'AutoFill formula. Copy and paste data as value
    Range("P3:Q3").Select
    Selection.AutoFill Destination:=Range("P3:Q " & MaxRowNum), Type:=xlFillDefault


'Copy and paste data as Value
    Columns("P:Q").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Application.CutCopyMode = False


End If
Loop

    Range("P3").Select
    
End Sub

Hi, your mistake is with the Colums "P " select and the Range("P3 " & MaxRowNum)
The macro could be simplified and I think this will do the job

Private Sub Missing_UserName_Dept()

Dim ws  As Worksheet
On Error GoTo errorFound
Set ws = Sheets("Simpat")
Err.Clear
On Error GoTo 0
ws.Select

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

Dim MaxRowNum As Integer
Dim RowNum As Integer

' Locate last filled row in column B (this istead of the loop)
MaxRowNum = ws.Cells(Rows.Count, 2).End(xlUp).Row


'Find the row with the name "Not indicated" and use a vlookup Function
' Find UserName cell = "NOT INDICATED"

For RowNum = 1 To MaxRowNum
    If UCase(Cells(RowNum, 16)) Like "*NOT INDICATED*" Then
        'Vlookup User Name from the file MISSINGUSERNAMEDEPT.xlsx
        Range("P3").FormulaR1C1 = "=IF(C[-3]="""","""",IFERROR(VLOOKUP(C[-3],MISSINGUSERNAMEDEPT.xlsx!R1:R1048576,2,0),""NOT INDICATED""))"
        'AutoFill formula. Copy and paste data as value
        Range("P3").Select
        Selection.AutoFill Destination:=Range("P3:P" & MaxRowNum), Type:=xlFillDefault
        'Copy and paste data as Value
        Range("P3:P" & MaxRowNum).Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
    End If
Next RowNum
Range("P3").Select
Exit Sub
    
errorFound:
If Err.Number > 0 Then MsgBox Err.Description, vbCritical, "Error#: " & Err.Number
Err.Clear
End Sub

I have added a few codes. I'm still facing the same problem .

Private Sub Missing_UserName_Dept()

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

Dim MaxRowNum, RowNum As Integer

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
Do While RowNum < 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("P3").FormulaR1C1 = "= VLOOKUP(C[-3],MISSINGUSERNAMEDEPT.xlsx!R1:R1048576,2,0)"

'Vlookup Dept from the file MISSINGUSERNAMEDEPT.xlsx

Range("Q3").FormulaR1C1 = "= VLOOKUP(C[-4],MISSINGUSERNAMEDEPT.xlsx!R1:R1048576,3,0)"

'AutoFill formula. Copy and paste data as value
    Range("P3:Q3").Select
    Selection.AutoFill Destination:=Range("P3:Q " & MaxRowNum), Type:=xlFillDefault


'Copy and paste data as Value
    Columns("P:Q").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Application.CutCopyMode = False


End If
Loop

    Range("P3").Select
    
End Sub

Of course it slows down your laptop or anything you're using, yo address the file MISSINGUSERNAMEDEPT in every loop.
Yoiu'll have to think up another strategy

you are inserting vlookup for the row with not indicated but then your filling down the formula anyway? and then your looping it for each row?

either remove the autofill or just autofill without loop?
also since your using a do while loop...is your rownum even incrementing?

Originally Posted by fluffyvampirekitten

My idea is to find the row which displays "Not indicated" under UserName & Dept Column in SIMPAT file.
And then open up another workbook called MISSINGUSERNAMEDEPT to find the UserName & Dept using the VLOOKUP function.
Hence , If data(UserName/Dept) is found from the MISSINGUSERNAMEDEPT , this data will be pasted over in the SIMPAT file else "NOT INDICATED" .

if your idea is to only update rows with not indicated then you shouldnt autofill at all
autofill will over write every row from 3 to maxrow and you are doing it every single time

your logic for the formula needs to be updated to only take in the rownum
however since your using do/while loop and not incrementing rownum...condition of

Do While RowNum < MaxRowNum

will never be met

also this

    '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

seems like an awfully complicated way to find maxrow
unless your data structure absolute calls for it...
is there a specific reason why did not you take up the suggestion provided by Keebellah

' Locate last filled row in column B (this istead of the loop)
MaxRowNum = ws.Cells(Rows.Count, 2).End(xlUp).Row

your vlookup is the error
this formula

Range("P3").FormulaR1C1 = "= VLOOKUP(C[-3],MISSINGUSERNAMEDEPT.xlsx!R1:R1048576,2,0)"

generates this

= VLOOKUP(M:M,MISSINGUSERNAMEDEPT.xlsx!$1:$1048576,2,0)

you cannot vlookup a column onto another column
also your missing sheet name

your formula should look something more like

"=VLOOKUP(RC[-3],'[MISSINGUSERNAMEDEPT.xlsx]Sheet1'!C1:C3,2,0)"

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....

Also, note that:
Destination:=Range("P3:Q " & MaxRowNum) has a space after the Q, that's not allowed "P3:Q n" is not a valid address.

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

Could i ask one more thing , Is it possible to replace "#N/A" to "NOT INDICATED" ?

you want this in built into your macro or are you going to just use it once?
if only once then i suggest you just highlight the column
Ctrl+H
Find "#n/a"
Replace with "NOT INDICATED"

forgot #N/A has special rules -
reading here if your interested


change code to

    If Cells(rownum, 16) = CVErr(2042) Then
        Cells(rownum, 16) = "NOT INDICATED"
    End If
        
    If Cells(rownum, 17) = CVErr(2042) Then
        Cells(rownum, 17) = "NOT INDICATED"
    End If

Well , Other cells can display "NOT INDICATED" but if both Cells(rownum, 16) and Cells(rownum, 17) are #N/A , it still shows #N/A and then the error message will pop up.

Thanks I will read up all the resources that you have given me . I really need it .

RC[-3] means the cell in the current row with an offset of -3 columns wo if your active cell is D3 then RC[-3] points to A3
And yes, evrytime you open the MISSING.... file it takes time and you think you have all the rows to look at 1048576, well that's a lot.
The code as it is is not good, but without really understanding what you want to do and why the fill of the entire P column, sorry it's all unclear.

There is a function ISNA to trap for these errors.
Of course there are mny sites to help.
This one is ok http://www.excel-easy.com/
This one I frequently visit to look at solutions and samples, most macros work also for 2013
http://www.cpearson.com/Excel/Topic.aspx
If you need help just post it
Happy coding