I have a code like below. The same code is runing several times in one macro, sometimes excel crashs during this step. It is only happen if I am using the excel for a long time and I have done many operations before. With fresh excel it always works I can do it even 100 times. I am wondering why? Can be that it is related to RAM usage in that moment. Is there any possibility to replace my code by something more stable?
Excel sometimes crash while vba is using vlookup
-
thedogg -
March 1, 2018 at 6:22 AM -
Thread is marked as Resolved.
-
-
-
Can you explain in words what you are trying to do? Be as detailed as possible. Also, it would be very helpful if you could attach a copy of your file.
-
I want to only know if I can somehow replace the current vlookup function byt something else (can be vlookup in different form). No more.
-
Instead of using an formula, it can be replaced with some VBA code to do the same thing. In order to do that, it would be easier for me to help if understood exactly what your formula is trying to do. That is why I asked you to explain in words what you are trying to do. Also, if you attach your file, it would make it easier to understand and test a possible solution.
-
My formula is trying to find the ID for part number in the file I am using the macro. I have a BOM and part numbers in one file, in second file I which is on network I have part numbers and related IDs.
In first file I want to find IDs using vlookup. -
-
We would need more information to help. We need to know sheet names, file names, in which columns the ID's and part numbers are found, etc. The easiest way to get all the needed information is to work with your actual files. Can you attach a copy of both files? De-sensitize the data if necessary.
-
Don't you have all this information in previous code? In general it works well but when I have one BOM in one worksheet and I have for example 100 BOMs - 100 worksheets then I run the code 100 times for each worksheet in entire workbook then it crashes excel, sometimes in BOM 10 (worksheet 10) sometimes for BOM 99 (worksheet 99), this is my problem
-
The problem is that without seeing how your data is organized, it's difficult to suggest and test a working code. That is why I asked that you post a copy of your two files.
-
Unfortunately I cannot attache the files because I have them in the office and I am writing from home. It the creshe happen then after PC reboot it works again. I am trying to figure out what is causing the problems.
-
OK. Can you explain in words what you want to do? Use a few examples from your data, referring to specific cells, rows ,columns and worksheets. Be as detailed as possible.
-
-
1. My macro is prepareing internal file with amound bill of materials defined by user. Every BOM is in separated worksheet. I can have just one or more than one, even 1k.
2. In column B I have part number and I want to find related ID for that part number from file strored on network.
3. In the file stored on network I have worksheet called IDs with all part numbers and IDs which are related to them.
4. I am runing the macro which includes formulas from 1st post and the macro is checing IDs for all numbers.
5. The loop is between all worksheets.
6. The excel sometimes crashs in that vlookup step. Sometimes it is for worksheet 1 sometimes it is for worksheet 20, so 20 times it works byt suddenly it crashes. It is always the same vlookup function from 1st post but for different worksheet only. -
Is the file stored on the network containing the ID's and part numbers called "DS_LOOKUIP_.xlsx"? This file contains multiple sheets containing the ID's and part numbers. Is this correct? In which columns are the ID's and part numbers in each sheet? In the destination workbook you said that the part numbers are in column B. Is that correct? In which column beside each part number do you want to return the corresponding ID? Are the part numbers unique or can there be multiple instances of the same part number in the destination workbook and the sheets in the source workbook?
-
DS_LOOKUIP_.xlsx - yes this is the file stored on network, ids and pn inside.
Part numbers can be duplicated but ID is always different. Vlookup is taking the first available ID. sorted: part number lowest to largest and ID largest to lowest.
File has more worksheets, related to different functions.In destination file I want to have ID in column I.
In destination workbook part numbers can be also repeted multiple times. -
I have been trying very hard to understand your situation but it is very difficult. Now that you mention additional worksheets and duplicate part numbers, it will be hard to suggest a working code without seeing your actual workbooks. Sorry.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!