Newbie trying to use VBA if/and function to logically compare two values and provide the corresponding cell value in a matrix

TheThinker

New Member
Joined
Jul 29, 2010
Messages
5
Hey all and thanks in advance for helping me with this. I'm currently working with Excel 2007 on WinXP Pro SP3. The file was originally created in Excel 2003, so it's saved as .XLS.

This is kind of difficult to explain, but I have two columns of calculated values (G and I) that I'm trying to compare to values in a matrix on another sheet so that a value within the cell at a particular point in the matrix can be selected. Basically, it's a risk response matrix and the values in G and I are impact and likelihood, respectively. You end up with a formula which, in English, appears like:

If G is less than a certain value on the Y axis of the matrix, and I is less than a certain value on the X axis of the matrix then select the value at the intersecting point.

I've gotten it to the point that it seems to pull some values, but they aren't correct at all.

Here's the code I'm working with:

Code:
Function rValue(pval As String) As String

    If pval = " " Then
            rValue = " "
            
    ElseIf pval = 0 Then
            rValue = " "
            
    ElseIf pval < Range("Assumptions!E12").Value And Range("I" & pval + 0).Value < Range("Assumptions!O7").Value Then
            rValue = Range("Assumptions!N12").Value
            
    ElseIf pval < Range("Assumptions!E12").Value And Range("I" & pval + 0).Value < Range("Assumptions!M7").Value Then
            rValue = Range("Assumptions!L12").Value
        
    ElseIf pval < Range("Assumptions!E12").Value And Range("I" & pval + 0).Value < Range("Assumptions!K7").Value Then
            rValue = Range("Assumptions!J12").Value
    
    ElseIf pval < Range("Assumptions!E12").Value And Range("I" & pval + 0).Value < Range("Assumptions!I7").Value Then
            rValue = Range("Assumptions!H12").Value
    
    ElseIf pval < Range("Assumptions!E12").Value And Range("I" & pval + 0).Value > Range("Assumptions!G7").Value Then
            rValue = Range("Assumptions!F12").Value
    
    ElseIf pval < Range("Assumptions!E11").Value And Range("I" & pval + 0).Value < Range("Assumptions!O7") Then
            rValue = Range("Assumptions!N11").Value

    ElseIf pval < Range("Assumptions!E11").Value And Range("I" & pval + 0).Value < Range("Assumptions!M7").Value Then
            rValue = Range("Assumptions!L11").Value
            
    ElseIf pval < Range("Assumptions!E11").Value And Range("I" & pval + 0).Value < Range("Assumptions!K7").Value Then
            rValue = Range("Assumptions!J11").Value
    
    ElseIf pval < Range("assumptions!E11").Value And Range("I" & pval + 0).Value < Range("Assumptions!I7").Value Then
            rValue = Range("Assumptions!H11").Value
    
    ElseIf pval < Range("Assumptions!E11").Value And Range("I" & pval + 0).Value > Range("Assumptions!G7").Value Then
            rValue = Range("Assumptions!F11").Value
            
    ElseIf pval < Range("Assumptions!E10").Value And Range("I" & pval + 0).Value < Range("Assumptions!O7").Value Then
            rValue = Range("Assumptions!N10").Value
            
    ElseIf pval < Range("Assumptions!E10").Value And Range("I" & pval + 0).Value < Range("Assumptions!M7").Value Then
            rValue = Range("Assumptions!L10").Value
            
    ElseIf pval < Range("Assumptions!E10").Value And Range("I" & pval + 0).Value < Range("Assumptions!K7").Value Then
            rValue = Range("Assumptions!J10").Value
            
    ElseIf pval < Range("Assumptions!E10").Value And Range("I" & pval + 0).Value < Range("Assumptions!I7").Value Then
            rValue = Range("Assumptions!H10").Value
            
    ElseIf pval < Range("Assumptions!E10").Value And Range("I" & pval + 0).Value > Range("Assumptions!G7").Value Then
            rValue = Range("Assumptions!F10").Value
            
    ElseIf pval < Range("Assumptions!E9").Value And Range("I" & pval + 0).Value < Range("Assumptions!O7").Value Then
            rValue = Range("Assumptions!N9").Value
            
    ElseIf pval < Range("Assumptions!E9").Value And Range("I" & pval + 0).Value < Range("Assumptions!M7").Value Then
            rValue = Range("Assumptions!L9").Value
            
    ElseIf pval < Range("Assumptions!E9").Value And Range("I" & pval + 0).Value < Range("Assumptions!K7").Value Then
            rValue = Range("Assumptions!J9").Value
            
    ElseIf pval < Range("Assumptions!E9").Value And Range("I" & pval + 0).Value < Range("Assumptions!I7").Value Then
            rValue = Range("Assumptions!H9").Value
            
    ElseIf pval < Range("Assumptions!E9").Value And Range("I" & pval + 0).Value > Range("Assumptions!G7").Value Then
            rValue = Range("Assumptions!F9").Value
            
    ElseIf pval > Range("Assumptions!E8").Value And Range("I" & pval + 0).Value < Range("Assumptions!O7").Value Then
            rValue = Range("Assumptions!N8").Value
            
    ElseIf pval > Range("Assumptions!E8").Value And Range("I" & pval + 0).Value < Range("Assumptions!M7").Value Then
            rValue = Range("Assumptions!L8").Value
            
    ElseIf pval > Range("Assumptions!E8").Value And Range("I" & pval + 0).Value < Range("Assumptions!K7").Value Then
            rValue = Range("Assumptions!J8").Value
            
    ElseIf pval > Range("Assumptions!E8").Value And Range("I" & pval + 0).Value < Range("Assumptions!I7").Value Then
            rValue = Range("Assumptions!H8").Value
            
    ElseIf pval > Range("Assumptions!E8").Value And Range("I" & pval + 0).Value > Range("Assumptions!G7").Value Then
            rValue = Range("Assumptions!F8").Value
    
    Else: rValue = " "
        
    End If
    End Function
And here's a link to the document:

https://docs.google.com/leaf?id=0B8...OTZjNjE1NzBlZDMy&sort=name&layout=list&num=50
 
Last edited:

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Probably the first thing I would do is go to using Select Case instead of all the If/ElseIfs. (ie.)
Code:
Function rValue(pval As String) As String
  rValue = " "
  Select Case pval
    Case Is < Range("Assumptions!E12").Value And Range("I" & pval + 0).Value < Range("Assumptions!O7").Value
      rValue = Range("Assumptions!N12").Value
    Case Is < Range("Assumptions!E12").Value And Range("I" & pval + 0).Value < Range("Assumptions!M7").Value
      rValue = Range("Assumptions!L12").Value
    Case Is < Range("Assumptions!E12").Value And Range("I" & pval + 0).Value < Range("Assumptions!K7").Value
          rValue = Range("Assumptions!J12").Value
    Case Is < Range("Assumptions!E12").Value And Range("I" & pval + 0).Value < Range("Assumptions!I7").Value
          rValue = Range("Assumptions!H12").Value
    Case Is < Range("Assumptions!E12").Value And Range("I" & pval + 0).Value > Range("Assumptions!G7").Value
          rValue = Range("Assumptions!F12").Value
    Case Is < Range("Assumptions!E11").Value And Range("I" & pval + 0).Value < Range("Assumptions!O7").Value
          rValue = Range("Assumptions!N11").Value
    Case Is < Range("Assumptions!E11").Value And Range("I" & pval + 0).Value < Range("Assumptions!M7").Value
          rValue = Range("Assumptions!L11").Value
    Case Is < Range("Assumptions!E11").Value And Range("I" & pval + 0).Value < Range("Assumptions!K7").Value
          rValue = Range("Assumptions!J11").Value
    Case Is < Range("assumptions!E11").Value And Range("I" & pval + 0).Value < Range("Assumptions!I7").Value
          rValue = Range("Assumptions!H11").Value
    Case Is < Range("Assumptions!E11").Value And Range("I" & pval + 0).Value > Range("Assumptions!G7").Value
          rValue = Range("Assumptions!F11").Value
    Case Is < Range("Assumptions!E10").Value And Range("I" & pval + 0).Value < Range("Assumptions!O7").Value
          rValue = Range("Assumptions!N10").Value
    Case Is < Range("Assumptions!E10").Value And Range("I" & pval + 0).Value < Range("Assumptions!M7").Value
          rValue = Range("Assumptions!L10").Value
    Case Is < Range("Assumptions!E10").Value And Range("I" & pval + 0).Value < Range("Assumptions!K7").Value
          rValue = Range("Assumptions!J10").Value
    Case Is < Range("Assumptions!E10").Value And Range("I" & pval + 0).Value < Range("Assumptions!I7").Value
          rValue = Range("Assumptions!H10").Value
    Case Is < Range("Assumptions!E10").Value And Range("I" & pval + 0).Value > Range("Assumptions!G7").Value
          rValue = Range("Assumptions!F10").Value
    Case Is < Range("Assumptions!E9").Value And Range("I" & pval + 0).Value < Range("Assumptions!O7").Value
          rValue = Range("Assumptions!N9").Value
    Case Is < Range("Assumptions!E9").Value And Range("I" & pval + 0).Value < Range("Assumptions!M7").Value
          rValue = Range("Assumptions!L9").Value
    Case Is < Range("Assumptions!E9").Value And Range("I" & pval + 0).Value < Range("Assumptions!K7").Value
          rValue = Range("Assumptions!J9").Value
    Case Is < Range("Assumptions!E9").Value And Range("I" & pval + 0).Value < Range("Assumptions!I7").Value
          rValue = Range("Assumptions!H9").Value
    Case Is < Range("Assumptions!E9").Value And Range("I" & pval + 0).Value > Range("Assumptions!G7").Value
          rValue = Range("Assumptions!F9").Value
    Case Is > Range("Assumptions!E8").Value And Range("I" & pval + 0).Value < Range("Assumptions!O7").Value
          rValue = Range("Assumptions!N8").Value
    Case Is > Range("Assumptions!E8").Value And Range("I" & pval + 0).Value < Range("Assumptions!M7").Value
          rValue = Range("Assumptions!L8").Value
    Case Is > Range("Assumptions!E8").Value And Range("I" & pval + 0).Value < Range("Assumptions!K7").Value
          rValue = Range("Assumptions!J8").Value
    Case Is > Range("Assumptions!E8").Value And Range("I" & pval + 0).Value < Range("Assumptions!I7").Value
          rValue = Range("Assumptions!H8").Value
    Case Is > Range("Assumptions!E8").Value And Range("I" & pval + 0).Value > Range("Assumptions!G7").Value
          rValue = Range("Assumptions!F8").Value
    End Select
End Function
(And you'll want to double check to make sure I got them all.)

That said,
I've gotten it to the point that it seems to pull some values, but they aren't correct at all.
How close is your original code getting you? (If it's giving the wrong results, then can you see any pattern to what it's doing?)
 
Last edited:

TheThinker

New Member
Joined
Jul 29, 2010
Messages
5
Thanks for the reply.

I've tried to troubleshoot by numbering each case (from 1 to 25) rather than having it return the text values in the matrix, but I've made some substantial edits since the last time I've done that. Now it seems as though it's only pulling values from the first column on the Y axis, so it's possible that the AND statement isn't working the way I want.

I'll try with Select Case and see where that gets me.

Edit: Just tried the code you provided with the same exact results.
 
Last edited:

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
the same exact results
Same results you were getting in the beginning?, or at the end where it only returns values from the first column on the Y axis?
 

TheThinker

New Member
Joined
Jul 29, 2010
Messages
5
Same results as I was getting with the if/and code, returning only the first values on the Y axis.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,214
Messages
5,509,873
Members
408,757
Latest member
Jamarr123

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top