Copying averages from multiple csv files to a summary xls using VBA

Hi shg and thanks for your reply. I tried the code and it also gives me a 1004 runtime error. Using F8 I could see the Cells(iRow, 1).Value = sFile line as being the last one turning yellow before the error message is given.

When I replaced the variable iRow in that line by nRow the macro did run without errors. Also the list of filenames in column A is generated. The other columns remain empty though and no averages are copied, so I'm not quite there yet.

Oh and thanks for the comment on uploading files. I'll keep it in mind.

Originally Posted by shg

So comment out the screenupdating line and step through the code.

I'm sorry but could you please rephrase that? As mentioned above I'm a beginner and I do not quite understand what you advise me to do

Thanks for the link shg, it was very helpful in getting to know the debug functionality. After a holiday and some additional reading I gave it another try and got the code to work. The problem was not assigning clearly what object was to be worked on by the program. In the end I did modify what kind of info was copied: instead of subtracting the average of line 90-109 from that of line 30-80, I now sort the columns first and just take the top 2-11 as signal and the bottom 10 as background since not all data files are the same. That I could do by recording a macro while opening a data file and performing the desired steps.

I just wanted to share my solution with you, as well as ask an additional question. Since not all data files are usable (some don't contain any peaks, others are shifted, hence my different approach in getting the average), I'd like to add something to the macro. As it opens each csv file in column A and calculates the averages in these files before copying to my master Data_Extractor file, I'd like the program to save the files in a different location (e.g. "Used") if the criterion of "at least one of the calculated values is over 0.05" is met. In that way, I can discard the data files that don't contain usable data and only continue with the "Used" files. The line I wrote to do that is just not working yet. That being said, the code as given below runs without errors and produces the desired result.

Please Login or Register  to view this content.

Thanks in advance for your help.

Dirk-Jan

Ah I just posted the version without the line that should send good files to the "Used" folder. The line I wrote is located at the very end of the macro:

Please Login or Register  to view this content.

As mentioned, this doesn't work yet. I suspect the If-statement is correct, but the saving isn't.

Ah I just posted the version without the line that should send good files to the "Used" folder. The line I wrote is located at the very end of the macro:

Please Login or Register  to view this content.

As mentioned, this doesn't work yet. I suspect the If-statement is correct, but the saving isn't.