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

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