Code stopping after first row

[gutterball]

I have a workbook that has two macro buttons. The first, "Import CSV" button manipulates a CSV and imports it onto the first worksheet. The second, "Check CSV" button highlights errors in the CSV.

With some help the "Check CSV code was updated to also check an array of values.

The updated code works if I manually manipulate the CSV and copy it into the worksheet, but it stops after the first row if I import the survey to the worksheet.

I cannot figure out why the code only checks the first row if the file is imported, ant help would be appreciated.

Below are the files I am using.

Good code:
https://dl.dropboxusercontent.com/u/.../GoodCode.xlsm

Broken code:
https://dl.dropboxusercontent.com/u/...rokenCode.xlsm

Test file:
https://dl.dropboxusercontent.com/u/...0/TestFile.csv

Manipulated test file:
https://dl.dropboxusercontent.com/u/...edTestFile.csv

[TMS]

Many people won't download files from external sources.

You would be better uploading to the forum.


To attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[gutterball]

Yes, sir.

please see the files below:

Broken code:
BrokenCode.xlsm

Good code:
GoodCode.xlsm

Manipulated test file:
ManipulatedTestFile.csv

Test file:
TestFile.csv

Desired results:
TestFileAFTER.xlsx

Thank you

[TMS]

In your TestFile.csv, after you run your import, column E is not blank below the real data. Hence, when you do End Up, it goes straight to the top ... and you only process one row. So, the problem is with your import code but I'm not sure where or why.

Regards, TMS

[TMS]

In the import code, this block of code, apart from not being very efficient, simply ends up adding a space to EVERY cell in column E ... via column J.

Please Login or Register  to view this content.

I guess your live data may have more data that is split out to be concatenated but, in this instance, it's just adding a space to what was in column E. The bad news is that, because you are doing it to every cell, you just end up with a space in 1,000,000+ cells.

Try it this way:

Please Login or Register  to view this content.

See the attached updated example.

Regards, TMS

[gutterball]

Thanks Trevor,

I didn't think the error was with the import code because it worked fine with the old checking code. After you first mentioned it, I was trying to adapt the code to work but kept running into errors with the worksheet match function. Because I didn't know how to get the same results without the " ". I am always learning. Thank you for sharing your knowledge.

[TMS]

You're welcome. Thanks for the rep.


I didn't change the functionality of your code. It still adds a space; it just does it for the rows with data rather than every cell in the column. And I've removed the cell, row and column selections wherever possible. The only one that is left in is the Range("A1").Select which is needed for the Conditional Formatting ... it can be a tad touchy.

Regards, TMS



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.