OT: VBA in Excel (solved)
All right, I normally don't post OT items as this is a Michigan SPORTS blog but my general inability to succeed with finding help elsewhere in the world that Google allows me to connect to has left me with little choice.
I'm currently working on writing an Excel macro to make my life easier. Hopefully, if everything works out properly, writing reports at work will be significantly faster. The thing is, I have no experience with VBA and I'm having some problems that I'm hoping the MGoBoard can help me with. Believe me when I say that I've tried my best to Google myself some answers but I've been thus far unsuccessful.
What I need to do is look at a range of cells and determine if the entries in column A fall into one of 3 categories which, for simplicity, we'll call cat1, cat2, or cat3. Every cell will be in one of those categories. Based on that, I need to copy columns A-C of all the rows that have cat1 data in their column A cells to a sheet that we'll call sheetcat1. I need to do the same thing cat2 and cat3- sending them to their own respective sheets.
I've written code that can succeed at copying entire rows over from the master sheet to the specific category sheets. So, the only problem is that I don't want the entire row to copy over as that will get rid of the other formulas that are currently in columns D-X in sheetcat1, sheetcat2, and sheetcat3. I did so with a moderate amount of modification to the second set of code shown here.
To adapt my current code to what I need it to do is somehow tell Excel to not select the entire row during the proper "For" loop but instead just select columns A-C. In the link I posted, that would be changing the "Set MyRange1 = c.EntireRow" (and the other similar "Union" line below that) so that the range that's being set for "MyRange1" is just columns A-C in row c.
This seems very simple but I just can't figure it out. Any help would be appreciated (that appreciated manifested through points). Again, I know that this isn't the best place for this but I figure there must be some CE majors on here that can help an old ChemE major alumnus remember some coding that he's forgotten over the years. Besides, what else do we have to talk about since we're not really discussing expansion possibilities?
Thanks.
Edit: I was able to heavily modify the code from the link I gave and now it works just fine. Thanks to MCalibur for the assist.
The CELLS function should do the trick. I'm on a mac right now, so I can't give you a specific code example. But I've always found the VBA help to be useful. If you've gotten this far in VBA you should be able to figure it out from there.
Off the top of my head I think it'll go something like this:
data = Range(Cells(desired_row,1), Cells(desired_row,3))
Then flip over to the right sheet and paste into the right location.
Hope that helps.
That's actually been my most recent attempt at getting this to work but there's a slight problem, the modified code doesn't have the equivalent of "desired_row" as a number but instead find the range of rows with data and then creates a new range based on what passes the various if tests. Since it's all based on ranges, there doesn't seem to be a specific row number that's being worked on.
Right now I'm attempting to modify the "For" loop so that there is a number that's actually counted and can thus be used in that format. I was hoping, however, that someone would know of a way to make the proper selection without knowing the row number that is being worked on.
Edit 1: I should note that it's being done this way precisely to avoid having to perform a lot of copy/paste operations since those take significantly longer to do 200-600 times then just figuring out which rows go to which sheet and performing the appropriate copy/past a maximum of 3 times.