ProbSevScore for making a Heat Map

  • There are plenty of posts asking for help with this common code and I have made it work for its intended purpose of multiplying the values for Likelihood and Consequence and placing the corresponding Risk no. from the adjacent cell in each row onto a heat map but I am now wanting to change the code below to add the 2 column values instead of multiplying them, help please.
    The formula used on the sheet is;


    =ProbSevScore($E5,G$2,$A$3:$A$42)


    ....and this is the vba function

  • Code Tags Added
    Your post does not comply with our Forum RULES. Use code tags around code.


    Posting code between

    Code

    tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.


    Highlight your code and click the # icon at the top of your post window.


    (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

  • Hi Carim. I am referring to the video you linked. Tomorrow morning I’ll attach a file with what I’m trying to achieve. Cheers


    Great ... :smile:


    Attaching your file ... will make things a lot easier ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hello,


    Thanks for the File ...:smile:


    Just to fully understand your objective ...


    Could you tell me if this function produces your expected results ... or not ...



    What do you mean by

    Quote

    Use the SUM of columns B and C instead of the PRODUCT


    Thanks for your clarification ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • By "Use the SUM of columns B and C instead of the PRODUCT" I mean that the original function on the heat map sheets uses the values in each row for columns B and C and multiplies them together, the resulting value determines which cell of the heat map the value of cell A is placed. I'm wanting the values in B and C to be added together instead of being multiplied and the value in cell A put in corresponding cell of the heat map.

  • Hello,


    A couple of remarks :


    1.

    Quote

    the original function on the heat map sheets uses the values in each row for columns B and C and multiplies them together ...


    As you can see in the macro posted above .... there is NO mathematical operation between the Two elements ( row & column ) ... the macro only uses them to locate the proper intersection ...


    2. Have you tested the Macro above ...??? since both 61 and 17 only appear once at the right intersection ...


    HTH

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Thanks heaps Carim, it's working a treat now. I appreciate the time you've sent helping me with this.:thanx:


    Glad it is all sorted out ...


    Thanks ... for your Thanks ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!