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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,422
Messages
5,837,142
Members
430,477
Latest member
roseinsydney

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