Change Cell if One of Multiple Values (VBA)

Simple code:

Please Login or Register  to view this content.

but efficient code is rarely the shortest, efficient code would almost certainly avoid the loop altogether, but you haven't posted that part...

CC

Here is all of the code:

Please Login or Register  to view this content.

Ok.

Simple answer, the proposed select case could easily be expanded, if my previous post doesn't make it clear, please look at the help, the help for this particular construct (select case) is very straightforward, anything I wrote here would basically just be a reiteration.

Slightly less simple, (as I mentioned before) loops are very inefficient. If your data set is more than ~1000 entries, you would be better served by writing your "Direct Material, Indirect Material... etc" to an array, then looping through the array and applying an autofilter to your dataset (record a macro to get syntax) for each array element, then applying the "rewrite" to the range(topcell, topcell.end(xldown)) for each array element.

Most awkward, and poignant, answer. Overwriting this kind of information is pointless. It takes hardly *any* memory to retain the original entries for your categories, so you should write a simple vlookup to return the "groupings" to an adjacent column - if you ever need to refer back to the granularities, you won't be banging your head against the wall for overwriting them with a stupid ruddy macro! Of course, if you are pivoting your data (which I'd probably recommend), you don't need even to group in source data, you can apply groups in pivot tables very easily.

I hope this all makes sense, please post back for more specific help on any aspect (I have deliberatley not gone into detail because depending on your choice, some of my recommendations are irrelevant), but (obviously?) I recommend you bin the macro altogether and simply retain your raw data and group it "after the data" rather than rewrite.

CC

Forgive me I am very new to VBA. I have learned a lot with recording macros and searching Google. It is very hard to find c=26 though. Even with limiters, the results are unbearable to look through. So I just have a few questions about the code in this thread. I have added the questions to the code. I am guessing my questions are basic, but as I said I am still learning.

Originally Posted by gema

Here is all of the code:

Please Login or Register  to view this content.

Wow, haven't seen this in awhile. I'll do my best to explain but I haven't used VBA in a couple years.

c=26 defines what column the code looks at when I referenced c. I like to define variables in VBA to make updating them easier. For example, lets say the file changed and I wanted to reference column 30 instead. Since the variable is defined, I would only have to change it in one place, instead of everywhere there is a (r, c).

This particular code needed to run on every row from 2 through the end (length of report was always changing). The r = 2 says to start on row 2, then count however many rows are used in column J and run on every row in that range. I don't remember this file, but I probably chose column J because it was the one row with the most complete data (had data in every row to the end).

Column 26 is the column that the functions are analyzing, but column J is used to determine how many rows the functions should analyze.

Hope this helps.

Yes it does thank you very much.