Fine-tuning VBA code

Dear all,

I'm using Excel 2007 to import large text files using VBA code. In the text file I'll say a bit more about in a moment, the the first column of numbers are longitude, the second latitude and columns from there on, data point values.

The first problem is that when a very small number is encountered (e.g. 4.2321E-02) in the text file, upon import to Excel, the 'number' is imported into the correct cell, yet the E-02 is put into the adjacent cell, causing the number to be split up - this also causes subsequent values to be shifted one cell further along than they should be. I was wondering therefore if there's a way of keeping the E-02 attached to the end of the number. I've noticed that in the text file sometimes there is a space between the number and the E-x part (most often for the latitude and longitude values) with often no space for the data point values. Any suggestions are of course welcome on how to get round this!

The second problem is a bit of an odd one. Upon import of the data, the final cell of each row and positions in the dataset where there is meant to be no value (ie. a blank cell), a question mark in a box appears (this doesn't appear in the text file). I'm wondering whether this is meant to be some kind of carriage-return marker or something akin to this. Anyway, I thought it would be easy to remove, just using find and replace to make the cell blank once more - however, as this isn't a text character, it won't let me copy and paste it into the find/replace box. So I'm a little confused how to get rid of this.

Each text file is around 300MB (told you it was a large import!), so I've uploaded a smaller sample one to www.megaupload.com in case you need to have a look - it's still quite big mind you. To view it, please go to the following website: http://www.megaupload.com/?d=6NN199FN and enter in the anti-spam code in the top-right corner.

The code as it stands is as follows:

Please Login or Register  to view this content.

Many thanks for your time and effort, I appreciate it.

Best wishes,
Steve