Code Efficiency - Adding formulas and exporting to text files

[bilbo85]

Hi there,

I am hoping someone may be able to offer suggestions about how I can make my code more efficient as it is very slow to run? Essentially it adds a formula in column A and a formula in column B, for any number of rows. It then exports each column into 2 separate text files.

Thanks in advance.

Please Login or Register  to view this content.

[harrisonland]

Without even looking at your export function, how about replacing this

Please Login or Register  to view this content.

With this:

Please Login or Register  to view this content.

Tim

[harrisonland]

Then... looking at your export routine, try using an array to set up the print and send it all to the file(s) in one fell swoop:

Please Login or Register  to view this content.

Getting rid of the loops will invariably make the code much faster. Maybe someone else can come up with a better solution (I'd still consider myself a beginner), but that could be a start.

Tim

[bilbo85]

Thanks, that definitely helped a lot!

The only issue now is that the data in the text file is all in one block whereas before it separated on each line. Do you know if I could get it back to line separated please?

Many Thanks

[harrisonland]

Sorry, don't get on here much during the week at the moment.

Strange, the text files from my sample are showing with each entry on a separate line (see attached)... Exactly the same output as when I use your code.

BTW, I realised that using char(10) as separation character doesn't really have an effect since print writes each value from the sCellx array on a separate line anyway.

Tim

[bilbo85]

Hey Tim,

They seem to be all in one block in your text files too? There is just one line with "ABCDE...".
Each row in excel should have one line in the text file so if there are 100 rows in excel, there should be 100 lines in the text file.

Appreciate your help with this.

[harrisonland]

Definitely showing multi-line output. See attachment.

[bilbo85]

Hmm yeah it's strange. If I open your text file on my phone, it shows multi line output but if I open it (or run it myself) in Notepad on my PC, it shows only one line.

[TMS]

Have you selected Word Wrap when you view the file(s) in Notepad?

[bilbo85]

No, Word Wrap is off..


Attachment 679493

[bilbo85]

Ok after some more digging, it seems it is a Notepad issue.

https://superuser.com/questions/3620...ing-linebreaks

"Notepad only recognises CR, LF (0x0d, 0x0a), whereas other sources might use CR only, or LF only."

Is there something in your code that I'd need to change to accommodate this? Perhaps char(10)?

[TMS]

Mmmm, you need to switch Word Wrap on.

[TMS]

Maybe change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and same for sCell2.

[TMS]

Thanks for the rep.