- #1
Yanick
- 399
- 22
Hello all,
A brief introduction to my skill set: I've taken an intro to C++ course which was geared toward Science/Math/Engineering students so I'm familiar with programming in general, conditions, loops, functions, pointers (or whatever its called when you call by ref/value etc), arrays (n dimensional) and even a bit of object oriented programming (we played with robots!), classes, objects etc. So I don't think you have to start from the very beginning with me but I'm no whiz at this stuff either and severely lack experience, I've written maybe 20 programs in C++ in my life.
I'm learning how to make macros in Excel to be more efficient in some of the work that I do which is pretty labor intensive. In a short summary the task requires me to import data from a .dat file, eliminate some unwanted stuff at the start of the file, and fit an equation to the data to get coefficients. I also have multiple trials per workbook so the task is repeated n number of times until I have done all of my replicates. Then I go onto another workbook which is a different set of conditions and do the whole thing all over again. I learned some basic stuff about recording macro's and it has made life a lot easier already but I'm hoping to optimize it a bit more by writing in/editing some of the code. I know very little about VBA (just read some tutorials and basic stuff online) so I'm not sure if what I want to do is even possible.
What the code does now is that it imports X and Y values into two columns (say A and B) from a .dat file that I put into a specific folder on my desktop. I haven't made the code graph a scatter plot or fit a line because each trial is somewhat unique and I'd rather do that myself. Now I looked at the code in the editor and saw that it always wants to put the data into column A, and if there is already data there it moves the old data over to make room. So if I've already run the macro once, there is data in A and B then, on the next run, it takes the original data and moves it to column C and D and imports the new data into column A and B. It is also wonky because it doesn't always resize the column length even though I did that during the recording. It seems to resize the first time the macro runs but leaves the columns wide on subsequent runs.
What I would like it to do is look at column A and B and if there is data there I want the macro to skip column C and import into column D and E. In other words I'd like there to be one empty column between each trial and I'd like each subsequent trial to go into a later column. I guess something akin to an if/if else statement is necessary? But then I need to declare a variable to store the column letter/number and add an appropriate amount to get the "skipping" thing going properly. I'm not even sure if I can do that in VBA.
Here is what I'm hoping to write the code to achieve (I guess this is pseudo-pseudo-code):
1. look at column A, if there is data there then look at column B etc until you get to a column with a blank cell.
2. When you hit a blank cell, check that the next cell has no data and if it doesn't then go import the data into that cell (the second blank cell), if it does then keep scanning the cells until you get to the condition of two consecutive empty cells.
3. Import into the second empty cell as usual.
It would also greatly help me if I could make some type of loop (while loop or do-while maybe?) which will import multiple .dat files in a specified folder. For example: I could just put in the 5 trials into a certain folder and run the macro once to get all five trials imported into one workbook. I have no idea how to set up an appropriate condition for that though, only thing I can think of is to try and number the files 1...n.dat and when the macro reaches the last number (say 5.dat) it will look for 6.dat, not find it, and end. That would require something like a counter I guess but I'm not sure I can do any of that kind of stuff in VBA (or any language for that matter).
Thanks in advance for any help you guys can offer. I'm not looking for people to do my work for me and welcome some links to materials which will explain some of the functionality/syntax of VBA to me. I also apologize if I am butchering terms, my professor was keen on getting us to learn the fundamental concepts and not just words and syntax.
A brief introduction to my skill set: I've taken an intro to C++ course which was geared toward Science/Math/Engineering students so I'm familiar with programming in general, conditions, loops, functions, pointers (or whatever its called when you call by ref/value etc), arrays (n dimensional) and even a bit of object oriented programming (we played with robots!), classes, objects etc. So I don't think you have to start from the very beginning with me but I'm no whiz at this stuff either and severely lack experience, I've written maybe 20 programs in C++ in my life.
I'm learning how to make macros in Excel to be more efficient in some of the work that I do which is pretty labor intensive. In a short summary the task requires me to import data from a .dat file, eliminate some unwanted stuff at the start of the file, and fit an equation to the data to get coefficients. I also have multiple trials per workbook so the task is repeated n number of times until I have done all of my replicates. Then I go onto another workbook which is a different set of conditions and do the whole thing all over again. I learned some basic stuff about recording macro's and it has made life a lot easier already but I'm hoping to optimize it a bit more by writing in/editing some of the code. I know very little about VBA (just read some tutorials and basic stuff online) so I'm not sure if what I want to do is even possible.
What the code does now is that it imports X and Y values into two columns (say A and B) from a .dat file that I put into a specific folder on my desktop. I haven't made the code graph a scatter plot or fit a line because each trial is somewhat unique and I'd rather do that myself. Now I looked at the code in the editor and saw that it always wants to put the data into column A, and if there is already data there it moves the old data over to make room. So if I've already run the macro once, there is data in A and B then, on the next run, it takes the original data and moves it to column C and D and imports the new data into column A and B. It is also wonky because it doesn't always resize the column length even though I did that during the recording. It seems to resize the first time the macro runs but leaves the columns wide on subsequent runs.
What I would like it to do is look at column A and B and if there is data there I want the macro to skip column C and import into column D and E. In other words I'd like there to be one empty column between each trial and I'd like each subsequent trial to go into a later column. I guess something akin to an if/if else statement is necessary? But then I need to declare a variable to store the column letter/number and add an appropriate amount to get the "skipping" thing going properly. I'm not even sure if I can do that in VBA.
Here is what I'm hoping to write the code to achieve (I guess this is pseudo-pseudo-code):
1. look at column A, if there is data there then look at column B etc until you get to a column with a blank cell.
2. When you hit a blank cell, check that the next cell has no data and if it doesn't then go import the data into that cell (the second blank cell), if it does then keep scanning the cells until you get to the condition of two consecutive empty cells.
3. Import into the second empty cell as usual.
It would also greatly help me if I could make some type of loop (while loop or do-while maybe?) which will import multiple .dat files in a specified folder. For example: I could just put in the 5 trials into a certain folder and run the macro once to get all five trials imported into one workbook. I have no idea how to set up an appropriate condition for that though, only thing I can think of is to try and number the files 1...n.dat and when the macro reaches the last number (say 5.dat) it will look for 6.dat, not find it, and end. That would require something like a counter I guess but I'm not sure I can do any of that kind of stuff in VBA (or any language for that matter).
Thanks in advance for any help you guys can offer. I'm not looking for people to do my work for me and welcome some links to materials which will explain some of the functionality/syntax of VBA to me. I also apologize if I am butchering terms, my professor was keen on getting us to learn the fundamental concepts and not just words and syntax.