receive positive or negative numbers

[lordjames]

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.

[mikerickson]

Questions about Excel are easier to address if you attached an Excel workbook rather than a Word document.

[Aussiebear]

Please explain the logic of;
1. "My goal is to receive a negative or positive number in one cell pertaining to data inputted manually into two other cells". Which two other cells?
2. "Cell B9 would show a result depending on which code is used pertaining to the data in cells A8 & A9." This means absolutely nothing to us. What code are you using? How does the result become achieved? What is the data in Cells A8 & A9,.... Numerals, Alphabetical or a string containing both?
3. "Any combination of these numbers and letters would be used." Am I correct in assuming that you mean, "That the value of A8 could be achieved by using any combination of values within the range AS2: AS20"? If for example, A8 equals the value 10 and within the range AS2:AS20 there were the values 5, 5, 8, -3, what determines which is correct 5+5 or 5+8-3?



Please have another think about what you are asking us here. Since we cannot stand in front of your computer or see over your shoulder, your current explanation of the issue you face is rather weak. As Mike suggests attach a workbook showing a before sheet and an after sheet

[lordjames]

I have numbers and letters located in three different ranges which shall be permanent within their own ranges. The ranges are AQ2:AQ9, AR2:AR12 AND AS2:AS20. Any one of the numbers or letters used within these three ranges may be entered in cells A8:A32. The letters (xx) will be inputted into cell A8 for my 1st data entry. My 2nd data entry shown is the number (3) which is in cell A9. Excel would search through the eight different criteria conditions I've listed as Cases in my VBA to produce the proper result in cell B9. (I do not know how to tell it to go to B9)
In this case the 1st condition meets its request as both the xx and the number 3 are in the range AQ2:AQ9. I used the - IF( & AND( & THEN only for clarity. I need numerals for results as shown in column B, not true or false answers. Moving downward cell B10 askes the same from cells A9:A10. This time the 2nd example shown works because the number (3) in cell A9 is from the range AQ2:AQ9 and the number (1) in cell A10 is from range AR2:AR12. If I need to input 8 criteria conditions for every cell from B9 to B32 and beyond then so be it.
The result is achieved with Excel selecting the case criteria that best matches the condition for each cell in column B. There is no addition or subtraction used. Kind of like saying if one cell is blue and another red then this cell would be green. No math. Cell B33 shows either a positive or negative result after the last entry in cell B32 is automatically retrieved. I don't understand the term 'String'. The (xx) is used for a number that Excel will not display correctly so to compensate I’ve also used the letter (x) in other programs.

[Aussiebear]

Look, I've had a look at your posted workbook and it still leaves a great deal to be desired. As I understand it at this point, you are looking for a formula to check multiple values against a set range and if true then allocate a predetermined value to the cell

Its my belief that you need to use something like index/Match to test the conditions for cell B9:
If(And(Index($AN$8,Match($AN$8,$AQ$2:$AQ$9),Index($AN$9,Match($AN9,$AQ$2:AQ $9),0,"34","")))

If you wish to learn more about Index Match go to www.contexture.com. Debra has some wonderful examples there.

[Paul_Hossler]

Not sure about your pseudo-code, but I'd use a user defined function something like this which I think is your pseudo-logic

Put this in B9 for example

Capture.JPG

Option Explicit

'IF(AN8=AQ2:AQ9),AND(AN9=AQ2:AQ9),THEN B9="34"

Function SomeFormula(MatchRange As Range, CompareRange As Range, ReturnValue As Long) As Variant
    Dim iMatch1 As Long, iMatch2 As Long
    
    On Error GoTo NiceExit
    iMatch1 = Application.WorksheetFunction.Match(MatchRange.Value, CompareRange, 0)
    iMatch2 = Application.WorksheetFunction.Match(MatchRange.Offset(1, 0).Value, CompareRange, 0)
    On Error GoTo 0
    
    SomeFormula = ReturnValue
    
    Exit Function
    
NiceExit:
    SomeFormula = CVErr(xlErrValue)
End Function

[lordjames]

Thank you for your reply. I will study this method as it points me to a new direction. I just need each cell in my result column to have eight different options to produce one to four different results.

[lordjames]

Thank you for your reply. This is way different than doing ActiveX Controls I'm used to. I just need each cell in my result column to have eight different options to produce one to four different results. Let me knock myself out a little more studying and I'll be back in touch.