Help with creating macro/formula based on cells meeting multiple criteria

--------------------------------------------------------------------------------

Here's what I'm trying to do.

I have non-static values in cells B1:B288 and corresponding non-static values in cells C1:C288. (NOTE: By non-static I mean the value in the cells could change. Value only, not type.)

In cells D1:D288 I want to perform a calculation on the value contained in the corresponding C cell based on the value of the corresponding B cell meeting certain criteria.

Values in the B column are all text values and are 4 unique values (QB, RB, WR, TE). Values in the C column are numeric and are derived as a result of a formula.

The objective is for the result in column D to be based on the following criteria:

1) What is the value of the corresponding B cell (i.e for D1 the corresponding cell would be B1)....QB, RB, WR or TE?
2) Which occurrence of previously established value are we dealing with beginning at the top of column B? (Hope this makes sense!)

A convoluted, confusing nested IF function is the hard way of doing it, so I'm looking for a simpler way. In "plain speak" this is what I want cells in column D to do:

"Look at the value in column B and determine which of the four possible values it is. Then, count the number of times this value appears beginning with cell B1 and including the cell we're working on. Based on those two criteria, perform a specific calculation on the corresponding value in column C."

I need to calculation in column D to reference a different cell for every 12th occurrence of each value in column B.

A simple example is:
C1*L1 for the first 12 occurrences of "QB" in column B.
C1*M1 for occurrences 13-24 of "QB" in column B
C1*N1 for occurrences 25-36 of "QB" in column B
C1*L2 for the first 12 occurrences of "RB" in column B
C1*M2 for occurrences 13-24 of "RB" in column B
C1*N2 for occurrences 25-36 of "RB" in column B

Etc, etc, etc,

Hopefully you're not lost and can help. A user-defined function or a macro would be great unless there is an Excel function that can handle this.

THANKS IN ADVANCE!

I got a bit lost in the mapping of the final part of your text as it appeared to inconsistent. However, the following UDF should act as a starting point to get you to where you want.

Please Login or Register  to view this content.

in D2 type

=MyFunction(B2)

The best way to see what is happening is to just use one formula and put a breakpoint (F9) in the first line of code. Then hitting F8 will take you through the code line by line and you can see the value of the variables by hovering the mouse over them.

Having a look at the Select Case statement in Excel VBA help will also be useful to you.