ExcelBanter - View Single Post

KeriM · 05:01 PM

I'm trying to do a somewhat complicated find in VBA.

I have a worksheet with column B having only two different values (eg. a and b). Column C has values that repeat for each value for column B. (eg. 1 and 2). The values that I want to return are in columns E and F.

So what I'm trying to do is this:

If columns B and C equal "a" and "1" (respectively), then subtract the values in E and F (on that row where the columns equal) and return that result on another worksheet.

Repeat for all the different combinations...a1, a2, b1, b2.

To illustrate:

Code:

Column B | Column C                Column E | Column F

   a             1                   70            50
   a             2                   80            30
   b             1                   60            20
   b             2                   40            20

I want to find a1 and write "20" in another worksheet.

Sorry if this is an overly long explanation, I feel like it's confusing to explain. Let me know if anything needs to be clarified. As always, any help is appreciated!

EDIT: If formulas can be used for this that'll work too. I can incorporate them into my VBA code. I'm messing with the Index and Match, but I'm not having any luck. I basically need to look up two different values and return two different values (and subtract them). I'm not sure if this is possible in a formula.