Macro for coding an action based on 3 columns of information

Hi all,

I'm doing a very time-consuming coding task that I think can be automated.
I'm coding errors in a procedural task environment, with the following steps: P, Q, R, S, T, Z,Complete_contract (post), and Next_order (end).
I want to code the position of an error action relative to its error position.

I tried writing out the conditionals below. I think they will work; I just need them translated into VBA.

Please Login or Register  to view this content.

Also, multiple error action cells in column E can be associated with just one cell in columns C and D respectively. I need to write code to make sure that Excel can code those special cases as well.

A workbook with sheets containing the uncoded and coded data sets are attached for clarity.

Thanks!

EL

Hi EL,

I'm just about to head home from work, or I'd have filled in a few more of your criteria, but I've tested this with the criteria entered so far, and it works fine.

May not be the best way, but it will evaluate all your criteria easily, and you should be able to put in the rest of the criteria without any problems. You can just copy paste the "IF" statements from the previous examples, and then change them to suit.

If not, I'm sure somebody can expand on this, or provide a better solution.

Please Login or Register  to view this content.

Hope that helps,

-Bob

Hi Bob,

Thanks for the help! I think I have the basic skeleton structure in, but when I try to run the Macro, nothing happens. Do I have to have a certain cell selected in order for it to work?

Here's a shortened version of the code I worked out based on what you gave:

Please Login or Register  to view this content.

Any help would be greatly appreciated!

Thanks!

EL

Hi again EL,

First, no cell needs to be selected, but you do need to be on the right sheet when running the macro.

What I am noticing however, and may have overlooked in the first place is that the cells in "C" don't contain "nil" only, they have a number and nil.

Given that, we can't directly compare (If C = "nil") I've used some workbook functions instead. Could have done a find, but ... effort!

Once you've filled in the rest of the criteria (copying the criteria into all of the case statements), it should work for all of them. At the moment, it will only work if D is "P".

Please Login or Register  to view this content.

-Bob

Hi again Bob,

I added the other criteria in and get a Microsoft Visual Basic error message that says "400"

The other thing I noticed is that when I do a break and check what the macro is doing, I notice that on the first run it finds "1 PR" in C, which is on row 618 of the attached workbook.

Are we running the same version of Excel? I've got Microsoft Office XP (2002) version.

Thanks!

EL

Bob,

Could it be possible that adding all those criteria (as I wrote in the original post) might be too large for a Module? Would I need to do a Class Module or something to include all the criteria? I tried running just the Case R criteria and it worked like a charm!

EL

Originally Posted by chozen86

Bob,

Could it be possible that adding all those criteria (as I wrote in the original post) might be too large for a Module? Would I need to do a Class Module or something to include all the criteria? I tried running just the Case R criteria and it worked like a charm!

EL

I wouldn't know to be honest, my level of knowledge doesn't extend that far.

BUT, if they're working fine as standalone, then perhaps just break them down into other Subs and call them as needed. That may work.

E.g.

Please Login or Register  to view this content.

...And so on?

-Bob