Trouble with Case or If/And

TheThinker

New Member
Joined
Jul 29, 2010
Messages
5
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.

My knowledge of VBA is severely limited, but so far I've tried Case and using If/And statements. Here's some of the code I've been 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("I16").Value < Range("Assumptions!O7").Value Then
            rValue = Range("Assumptions!N12").Value
            
    ElseIf pval < Range("Assumptions!E12").Value And Range("i16").Value < Range("Assumptions!M7").Value Then

            rValue = Range("Assumptions!L12").Value
        
    ElseIf pval < Range("Assumptions!E12").Value And Range("I16").Value < Range("Assumptions!I7").Value Then
        
            rValue = Range("Assumptions!H12").Value
    
    ElseIf pval < Range("Assumptions!E12").Value And Range("I16").Value > Range("Assumptions!G7").Value Then

            rValue = Range("Assumptions!F12").Value
    
    ElseIf pval < Range("Assumptions!E11").Value And Range("I16").Value < Range("Assumptions!O7") Then

            rValue = Range("Assumptions!N11").Value

    ElseIf pval < Range("assumptions!e11").Value And Range("i16").Value < Range("Assumptions!m7").Value Then

            rValue = Range("Assumptions!l11").Value
    
    ElseIf pval < Range("assumptions!e11").Value And Range("i16").Value < Range("Assumptions!i7").Value Then
        
            rValue = Range("Assumptions!h11").Value
    
    ElseIf pval < Range("assumptions!e11").Value And Range("i16").Value > Range("Assumptions!g7").Value Then

            rValue = Range("Assumptions!f11").Value
            
    Else: rValue = " "
        
    End If
    End Function

I've tried a similar setup using Case rather than If. Also, I have no idea how to reference the "I" column so that it's dynamic in the code. I was going to tackle that once I got it to give me the correct values. Thanks so much in advance for any help with this, it's greatly appreciated.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Watch MrExcel Video

Forum statistics

Threads
1,122,468
Messages
5,596,307
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top