Slight Tweak to Selection and Update Program

Ssaqibh and Andrew Mann wrote a very useful selection and update Macro for me that needs a "tweak".  Attached is the WB/WS and it's this simple;

When you check the box in Col. F, the values in Col K change appropriately.  The values in Col. R should reflect the new changes.

That's it!

Thank you in advance,

B.
Compling-the-Value-List-v6.xlsm
Bright01Asked:
Who is Participating?
 
Saqib Husain, SyedEngineerCommented:
Ok, I have inserted the code in this file.
Copy-of-Compling-the-Value-List-.xlsm
0
 
andrew_manCommented:
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
0
 
andrew_manCommented:
I suggest you should tell me the calculation rather than give me a formula.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Bright01Author Commented:
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.
0
 
andrew_manCommented:
Okay with Thanks!

Andrew Man from Hong Kong
Compling-the-Value-List-v7.xlsm
0
 
andrew_manCommented:
Please use version 8, if  it is not reflected in Cells in column R.
Compling-the-Value-List-v8.xlsm
0
 
andrew_manCommented:
Dear B,

Would you mind to tell me where are you?

It seems very interesting in your case.

Andrew Man from Hong Kong
0
 
Bright01Author Commented:
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
0
 
andrew_manCommented:
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
0
 
Saqib Husain, SyedEngineerCommented:
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

Open in new window

0
 
Bright01Author Commented:
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.
0
 
andrew_manCommented:
I dun like the above code, it need more calculation.
0
 
andrew_manCommented:
Yes, it can updated at version 7.
0
 
Saqib Husain, SyedEngineerCommented:
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.
0
 
andrew_manCommented:
Yes, tried.  too many calculation.....
0
 
andrew_manCommented:
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
0
 
Bright01Author Commented:
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.
0
 
andrew_manCommented:
Dear B,

Have you try the version 7?

Andrew
0
 
andrew_manCommented:
I want to know what wrong in the version 7
0
 
andrew_manCommented:
Dear B,

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

Andrew
Compling-the-Value-List-v10.xlsm
0
 
andrew_manCommented:
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)"

Open in new window


Andrew Man
0
 
Bright01Author Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.