Optimize code

**DarkKnightLupo** · 02-05-2014, 05:49 AM

Hi all, I'm new here and I have a question and I hope you guys could help me out.
In the code on the on the bottom of this message I'd like to optimize, for example, Somethimes I have 10 PCB-flies (PCBS01-PCBS10), somethimes I get more, for example (PCBS01-PCBS27), is there a way to easily adjust everything by using loops or something? Also every collumn I copy from one file must be placed on the adjacent collumn and at the end I want as you can see have an extra collumn with average, and also one with the stdev. If more info required or if this impossible, please let me know

Thanks and greetings from Belgium!

Please Login or Register  to view this content.

**teh.format** · 02-05-2014, 06:18 AM

If u see flickering on the screen I would add at beggining

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

at the end.

**Izandol** · 02-05-2014, 06:22 AM

You may try:

Please Login or Register  to view this content.

**DarkKnightLupo** · 02-05-2014, 06:27 AM

Already Big thanks Izandol!

however when I add the code I get a err on:

Set wb = Workbooks.Open(Filename:=csPATH & "PCBS01.CSV")

**Izandol** · 02-05-2014, 06:40 AM

My mistake in file path:

Please Login or Register  to view this content.

**DarkKnightLupo** · 02-05-2014, 07:24 AM

thanks Izandol, should have seen that one too, mybad.

Workbooks("Dimitri04Feb2014Analysis.xlsx").Range("A1").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Here I tried several things like this but still not working, "Subscript out of range" Is the problem

Windows("Dimitri04Feb2014Analysis.xlsx").Column("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

edit: maybe because I should change the extension now by .xlsm ?

**Izandol** · 02-05-2014, 07:48 AM

If file is saved as .xlsm then yes you must change this. If it is file with code we may use better syntax:

Please Login or Register  to view this content.

**DarkKnightLupo** · 02-05-2014, 08:31 AM

Ok Thanks a lot, it works great now!
You are a Hero

**DarkKnightLupo** · 02-05-2014, 08:34 AM

Ok one nore esthetical question,

Range("B2").Value = "PCBS01"
Range("B2").AutoFill Destination:=Range("B2:L2"), Type:=xlFillDefault

L2 is for 11 files, but can you adapt it to the number you modify, for example 27 zithout counting everytime the Range ?

it is not that important!

**Izandol** · 02-05-2014, 08:52 AM

Of course:

Please Login or Register  to view this content.

**DarkKnightLupo** · 02-05-2014, 10:14 AM

Hey, I got another interesting question I think,
Imagine You have files with Names PCBS02, PCBS05, PCBS06, PCBS10 for example, so there is no regular connection and PCBS01, PCBS03, PCBS04, PCBS07 .. does not exist, is it possible to search for the files and putting them next to each other when I set n=10, because now I'll get a err when the file +1 is not there?

**Izandol** · 02-05-2014, 10:37 AM

Do you know the names in advance, or will you only process all CSV files in the folder? Which file must have two columns copied?

**DarkKnightLupo** · 02-05-2014, 10:49 AM

All the files exist out of three columns, The first one is for every file the same and the second one are the different results. ( so I need the first one, and all the second columns). I dont know the names in advance because they can change from experiment to experiment..., but also the total number of files changes but thats easy to look up.

To better Explain, S01, S02 is the number of the measurement, So it is possible I measure 10 times in a row the same thing, for example PCB, but it ia also possible I measure first 1 Reference, 3 Pcb, 2 A en 2 B, that woul give me a data set with names REFS01, PCBS02, PCBS03, PCBS04, AS05, AS06, BS07, BS08, You understand what I mean:p

Then I want in Excel a sheet with PCBS together, another sheet with REFs together, another sheet with As together ...

**Izandol** · 02-05-2014, 11:24 AM

I think I understand. You may try:

Please Login or Register  to view this content.

**DarkKnightLupo** · 02-05-2014, 12:09 PM

Ok this works very good, the only problem is that I get trouble calculating the average and the STDEV, originally I should have replaced -11 by -n, but now, -lCounter doesnt works...

**Izandol** · 02-05-2014, 12:11 PM

You may use fixed column to start - I assume column 2:

Please Login or Register  to view this content.

**DarkKnightLupo** · 02-05-2014, 12:17 PM

Ok thanks of course

,

You are a real pro, You really helped me out man, I hope I was not too annoying with stupid questions, Big Thanks o Izandol!

**Izandol** · 02-05-2014, 12:24 PM

You are welcome.

No annoying at all - questions are good! This is how we all learn.

**DarkKnightLupo** · 02-18-2014, 06:21 AM

Hi Izandol,

I'm back again, the code worked pretty well untill I realised It loads in files in a random order instead of the 1->x order ( now it is 1, 12 ,14, 3 ,5 ,... for example)
\. How can I force the sequence of the files I take my collumns of the way it should be so that first col comes from S01, 2nd from S02, etc...
Thanks already!

Here you can find the code:

Please Login or Register  to view this content.

**Izandol** · 02-18-2014, 12:25 PM

I have not tested:

Please Login or Register  to view this content.

**DarkKnightLupo** · 02-19-2014, 05:28 AM

Hi, no error but it still imports the files in a random order...

For ease I renamed the files to PCBDRIFTS1 instead of PCBDRIFTS01 and so on..

Thanks anyhow!

**Izandol** · 02-19-2014, 05:43 AM

For ease I renamed the files to PCBDRIFTS1 instead of PCBDRIFTS01 and so on..

This will break the sorting because in alpha sort PCBDRIFTS10 will come before PCBDRIFTS2. If you will name the file PCBDRIFTS01, PCBDRIFTS02 et cetera the sort will work.

**DarkKnightLupo** · 02-19-2014, 08:58 AM

Thank You !! It seems to work fine now!

Optimize code

LinkBack

Thread Tools

Rate This Thread

Display

Optimize code

Re: Optimize code

Re: Optimize code

Re: Optimize code

Re: Optimize code

Re: Optimize code

Re: Optimize code

Re: Optimize code

Re: Optimize code

Re: Optimize code

Re: Optimize code

Re: Optimize code

Re: Optimize code

Re: Optimize code

Re: Optimize code

Re: Optimize code

Re: Optimize code

Re: Optimize code

Re: Optimize code

Re: Optimize code

Re: Optimize code

Re: Optimize code

Re: Optimize code

Thread Information

Users Browsing this Thread

Similar Threads

Optimize a slow code...

Optimize code

Optimize Alphabetizing VBA Code

How can I optimize/simplify that code ?

Optimize VBA code

Bookmarks

Bookmarks

Posting Permissions