My goal is to receive a negative or positive number in one cell pertaining to data inputted manually into two other cells. Data would be inputted into cells A8 & A9 for example. Cell B9 would show a result depending on which code is used pertaining to the data in cells A8 & A9. I have eight different codes. Cells A8 & A9 would match numbers located in one of three different ranges: AQ2:AQ9, AR2:AR12 or AS2:AS20. Any combination of these numbers and letters would be used. Once established I would continue to move down the column, A9:A10, B10 as such.
If(and(or(else statements only give true or false results and I need numbers. Select Case seems the way to go but I haven’t any luck with syntax. Do I use Sub (by Val target AS Range)like that? Strings? I’ve tried Worksheet and Modules? I’ve received every error you can think of. I’ve had success with Comboboxes and command buttons in the past but this seems more complex. The Web sites I study tell me, ‘you visited this site many times’, again and again. Four weeks I’ve been at this.
I’m using Windows 7 Pro with Excel 2010. – Attached are my before and after Worksheet with a Range example.
In the middle screen shot my expected results are shown in cells B9:B11.
My formulas would be as follows:
A8 = AS2:AS20 AND A9 = AQ2:AQ9 THEN B9 = 17
A9 = AQ2:AQ9 AND A10 = AR2:AR12 THEN B10 = -8
A10 = AR2:AR12 AND A11 = AS2:AS20 THEN B11 = -4
In other words two cells must be something to give a result.
I have eight categories of criteria you’ll see in my code. Above I only show: AS-AQ, AQ-AR and AR-AS for space sake.