A useful Risk Liklihood Matrix

[gibbo1715]

Hi All

I needed to make a quick Risk/ Likelyhood matrix and I ve made one that does what i wanted but i had two thoughts so decided to post it here

Firstly this might be useful to others so feel free to use this for whatever you want

and

secondly my code is never as good as it could be so others here can probably suggest improvements I should make

let me know your thoughts

Gibbo

[mdmackillop]

Hi Gibbo.
It looks a useful function. We use this type of analysis in Health and Safety assessments, albeit with fewer categories.
A couple of suggestions.
Use With statements to avoid repeating objects.
You can use Ascii numbers to manipulate letter values, rather than long select statements, which can make your code more flexible.
Sheet modules are normally reserved for Sheet Events and associated code. I would tend to put your code in a standard module.


'Clear Previous and Reset Values
With Sheets("Chart")
.Range("C3:L12").ClearContents
.Range("C3:L12").ClearComments
.Range("C3:L12").Interior.ColorIndex = xlNone
End With

x = 0
y = 0
z = 0

'Loop through
For i = 3 To 100
' Get Column
RL = Range("C" & i).Value
If RL = 0 Then
Col = "0"
Else
x = x + RL
Col = Chr(66 + RL)
End If
'Get Row
LH = Range("D" & i).Value
If LH = 0 Then
Row = "0"
Else
y = y + 13 - LH
Row = 13 - LH
End If

[gibbo1715]

that is much better, hadnt considered doing it that way, im also thinking of making the add comment part a seperate function when i get a chance

thanks for the help

Paul