Slight Tweak to Selection and Update Program

Dear B,

Your formula has some problems.  Please verify.

=IF(F9="","",IF($I$2=1,H9,IF($I$2=2,I9,IF($I$2=3,J9,""))))

Thanks

Andrew Man

I suggest you should tell me the calculation rather than give me a formula.

Andrew,

Thanks for responding.  The calculation is the formula.  When you click on Column F, a check mark show up and the value is applied to cell K.  When you fire the macro on "Select Value" button, it changes the selection value and repopulates cell K.  This is not reflected in Cells in column R.  

Does that clarify it?

Thank you,

B.

Okay with Thanks!

Andrew Man from Hong Kong
Compling-the-Value-List-v7.xlsm

Please use version 8, if  it is not reflected in Cells in column R.
Compling-the-Value-List-v8.xlsm

Dear B,

Would you mind to tell me where are you?

It seems very interesting in your case.

Andrew Man from Hong Kong

Andrew,

I'm in NC USA.  But travel to China about twice a year.

Here is version 9.  The version you sent me had no changes I could see.  

B.
Compling-the-Value-List-v9.xlsm

Dear B,

You can use the version 7, and just input the new value.  I dun think we need press another key again!

Andrew Man

Sub sumup()
Const Input_Range = 9
Const Output_Range = 3
Const Output_CS = 17
Const Output_CE = 18
Const Input_CM = 6

Dim O_R As Integer
'Output Row
Dim I_R As Integer
'Input Row
Dim Total As Double

'Total

'**** Clear the previous output ****
O_R = Cells(Rows.Count, Output_CS).End(xlUp).Row
    If O_R >= Output_Range Then
    'Column and Row Output
    Range(Cells(Output_Range, Output_CS), Cells(O_R, Output_CE)).ClearContents
    End If
'Column Explanation
O_R = Output_Range


'Where the checkmark is
I_R = Cells(Rows.Count, Input_CM).End(xlUp).Row
If I_R >= Input_Range Then
'Input row
For I = Input_Range To I_R
With Cells(I, Input_CM)
If .Value = "P" Then
'Output results here
Cells(O_R, Output_CS).Value = .Offset(0, 1).Value
Cells(O_R, Output_CE).Formula = "=" & .Offset(0, 5).Address
Cells(O_R, Output_CE).NumberFormatLocal = _
        "_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""??_);_(@_)"

Total = Total + .Offset(0, 5).Value
O_R = O_R + 1
End If
End With
Next
Cells(Output_Range - 1, Output_CS).Value = "Total"
With Cells(Output_Range - 1, Output_CE)
.FormulaR1C1 = "=sum(r[1]c:r[" & O_R & "]c)"
.NumberFormatLocal = _
        "_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""??_);_(@_)"
End With
Else

MsgBox "Please select what you wanted! Thanks!"
End If

End Sub

Select allOpen in new window

ssaqibh and Andrew,

Open the worksheet and press the SELECT VALUE button.  This changes the amount value in column K that is not then reflected in Column R.

That's the additional functionality added to the original v7 code.

Make sense?

B.

I dun like the above code, it need more calculation.

Yes, it can updated at version 7.

Did you try my code? You will have to check/uncheck one of the boxes at least once to be able to use the select value as desired.

Yes, tried.  too many calculation.....

Dear ssaqibh,

You dun really understand my original coding!

I dun want to use too many functions.  Moreover, my version 7 is completely fulfill the client requirement.

Warmest Regards,

Andrew Man from Hong Kong

ssaqibh,

Yes.  I added your code to v9.  When you check the box, the code does update the value in col. K.  But when you click the blue button and fire the Macro for what I added (Benefit Range), it is only reflected in col. K, not the sum col. R.  

You should be able to check the value, have the value that is currently selected (Conservative, Realistic or Aggressive) placed in col. K. Then if you wish to change from the selected risk (Conservative, Realistic or Aggressive) the model should reflect that in the total Col. (R).

Make sense?

B.

Dear B,

Have you try the version 7?

Andrew

I want to know what wrong in the version 7

Dear B,

I want to keep my original design, then I created the version 10.

Andrew
Compling-the-Value-List-v10.xlsm

Dear B,

I dun like the below coding.  It is vitiating my original coding!

Cells(O_R, Output_CE).Formula = "=" & .Offset(0, 5).Address
.FormulaR1C1 = "=sum(r[1]c:r[" & O_R & "]c)"

Select allOpen in new window

Andrew Man

Ssaqibh,

Great! Just what I needed for making it even better.

Thank you,

B.

Andrew,

This new code has the ability to add "risk" to the values.  Thanks for chiming in.

B.