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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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?

also this

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

your vlookup is the error
this formula

Please Login or Register  to view this content.

generates this

Please Login or Register  to view this content.

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

your formula should look something more like

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

will never be met

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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"

if you want it part of the code

Please Login or Register  to view this content.

add two more IF blocks

Originally Posted by humdingaling

if you want it part of the code

Please Login or Register  to view this content.

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

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


change code to

Please Login or Register  to view this content.

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.

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 .

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