Stuck on infrequent errors in code

  • I have written some code that brings in a .dat file data (4 columns of survey data). These columns can be up to 100 000 rows or more. When I run the macro it occassionally stops copying or putting the values in after a certain amount of cells (which varies) and I can't work out why. I have assumed that it is because the macro is moving on to the next line of code before the previous "pasting" of values has completed maybe? I am only pretty new to VBA and maybe I haven't used the correct variable type maybe?? I have added some loops in after these actions today and it seems ok so far but I would love some feedback if anyone can help me out PLEASE... This is my first post on this forum, hopefully I have done it correctly.

  • Have you tried importing using Get & Transform Data from the Data Tab. From what you say it should basically be a text file, maybe change the file extension to csv first.

  • Attachments if you wanted to see what I am doing. I can get this to run fine on my machine, but when a colleague runs it he nearly always has it fail somewhere in the final stages, i.e. when you open the final .csv file it creates data is missing from columns A and B after cells 2000/10000 or 20000 depending upon how his machine is feeling on the day. Makes it hard for me to fault find when he is remote to me.||

  • I ran your code and it converted the dat file to csv file and reformatted it. I then used the Get Data function in the Data Tab choosing From Txt/CSV and it imported manually with no issues

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!