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:
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.
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.