VBA to paste .csv data into every 2nd line within worksheet

Hi,

I've posted previously to this forum, and received a great VBA solution, so I'm hoping someone could assist me again. I have a comma delimited text file that I need to import using VBA into a spreadsheet starting at A5, but I want the import to paste into every second line because I have formulas in the worksheet that will make a calculation underneath each imported line.
Eg.

Screenshot.png

(the blue lines have the formulas).

I have searched this forum, and google for several days now, but can't find a VBA solution. Would someone be able to assist?
Thanks in advance,
Stuart

Hi Kalbasiatka,
Thanks for your prompt reply, and greetings to Belarus from Australia. There's a lot of new commands in that script that I've never seen before. When I run it, I get a popup that says "Type mismatch". When I step into the code, the line which gives this error is

a = Split(row_, ",")

It's definitely got nothing to do with the character set (I saved the .csv file as UTF-8 using Notepad). Just to make sure it's not because the script is looking for a carriage return Chr(13), I even tried the line feed Chr(10), and that didn't work either. Do you have any further ideas?

Thanks again for your assistance.
Stuart

Hi Kalbasiatka,

I have amended your code to fix the "Type mismatch" error (by removing the brackets after Dim a), and since it's easy for me to get the text file to save as tab delimited, I decided to make the code work with a tab delimited file so I don't have to deal with all the inverted commas.

Please Login or Register  to view this content.

I now have two issues, which are possibly related.

1. When the data pastes, the formula doesn't seem to recognise the pasted data in the cell as a date unless I click on the cell and hit Enter, and same with the amount cell. I wondering if this is because there is a special character also in the cell, and the formula can't handle special characters mixed together with values.

2. When the VBA runs, I get a window with just "400" showing, and no other information. I can make it go away if I change the line

For Each row_ In Split(txt, Chr(13) + Chr(10))

to be

For Each row_ In Split(txt, Chr(13))

but then the first cell in each row has a line break before the data (which acts as a special character and stops the formula from working, which makes me think these two issues are related). The character set of the file is MS-DOS (PC8), but I couldn't find a CreateObject("ADODB.Stream").Charset equivalent to that, so I left it as UTF-8.

I'll send the data file to you in a private message.

Thank you very much for your patience, and for your help on this one!

For anyone wanting to see the final solution...

Since a comma delimited file has the added complication of needing quotation marks to stop genuine commas from being misread as a new field, I decided to make the adjustments necessary to work with a tab delimited file instead. The code is -

Please Login or Register  to view this content.

Thanks again Kalbasiatka.