Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a loop
I have made a spreadsheet that the user will input a few variables in a
column, and from these variables, file paths are created / specified in certain cells of that column. I then created a macro that will look at these file names / paths, find the specified files, convert them a *.csv file to a *.xls file, take the data in these files and put it into a seperate worksheet, and get an average for all of the files in that specified column. The macro then repeats the process, but it looks at the next column over (ie B was the first column, then it goes and looks at the C column then onto D then E etc.). The only way I knew how to get it to repeat and look at the next column was to re-write a seperate sub-macro for each set of columns (macro 1-10) specifying B cells for macro 1, C cells for macro2, D cells for macro3 etc. (Yes, I re-wrote the macro 10 times). I now have a file that is enourmously large, 120 MB, and runs as fast as a extinct dinosaur. Could someone please give me some help on how I can have "one" macro that will repeat and do the same functions it does now. But each time it repeats, it moves to the next column over for the specified file paths and writes a seperate work-sheet for each set of files? I am very new at doing stuff like this and it has taken me weeks to get his far (and a large bottle of Advil). Any help would be appreciated. Thanks, Ibuprofen |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a loop
so which part are you confused in?
the single-macro problem or the taking-the-average process? i don't really understand about the taking-the-average process... my assumption the single-macro problem would be: you select for example cell b2, run the macroB. once it done, you select c2, run the macroC, and so on. You used selection or selection.value in your macro to take information from the cells. so, replace "selection" or "selection.value" in one of your macros with "selection.offset(0,i)" or "selection.offset(0,i).value". for i=0 to 9 [copy one of your macro here] ... ... next i the following would be good too suppose your macro name is MacroB, modify sub MacroB so it can carry a parameter -- Sub MacroB(colNumber as Integer) then call it this way: for i=0 to 9 MacroB(i) next i Regards "Ibuprofen" wrote: I have made a spreadsheet that the user will input a few variables in a column, and from these variables, file paths are created / specified in certain cells of that column. I then created a macro that will look at these file names / paths, find the specified files, convert them a *.csv file to a *.xls file, take the data in these files and put it into a seperate worksheet, and get an average for all of the files in that specified column. The macro then repeats the process, but it looks at the next column over (ie B was the first column, then it goes and looks at the C column then onto D then E etc.). The only way I knew how to get it to repeat and look at the next column was to re-write a seperate sub-macro for each set of columns (macro 1-10) specifying B cells for macro 1, C cells for macro2, D cells for macro3 etc. (Yes, I re-wrote the macro 10 times). I now have a file that is enourmously large, 120 MB, and runs as fast as a extinct dinosaur. Could someone please give me some help on how I can have "one" macro that will repeat and do the same functions it does now. But each time it repeats, it moves to the next column over for the specified file paths and writes a seperate work-sheet for each set of files? I am very new at doing stuff like this and it has taken me weeks to get his far (and a large bottle of Advil). Any help would be appreciated. Thanks, Ibuprofen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with creating a loop | Setting up and Configuration of Excel | |||
creating a loop | Excel Discussion (Misc queries) | |||
Creating a loop | Excel Discussion (Misc queries) | |||
Need help creating Loop | Excel Discussion (Misc queries) | |||
Creating a For Loop | Excel Programming |