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
Bookmarks