Solved

Slight Tweak to Selection and Update Program

Posted on 2013-12-03
22
175 Views
Last Modified: 2013-12-03
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
0
Comment
Question by:Bright01
  • 14
  • 5
  • 3
22 Comments
 
LVL 4

Expert Comment

by:andrew_man
ID: 39692500
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
 
LVL 4

Expert Comment

by:andrew_man
ID: 39692505
I suggest you should tell me the calculation rather than give me a formula.
0
 

Author Comment

by:Bright01
ID: 39692517
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
 
LVL 4

Expert Comment

by:andrew_man
ID: 39692534
Okay with Thanks!

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

Expert Comment

by:andrew_man
ID: 39692546
Please use version 8, if  it is not reflected in Cells in column R.
Compling-the-Value-List-v8.xlsm
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39692563
Dear B,

Would you mind to tell me where are you?

It seems very interesting in your case.

Andrew Man from Hong Kong
0
 

Author Comment

by:Bright01
ID: 39692765
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
 
LVL 4

Expert Comment

by:andrew_man
ID: 39692783
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39692875
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
 

Author Comment

by:Bright01
ID: 39693001
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
 
LVL 4

Expert Comment

by:andrew_man
ID: 39693016
I dun like the above code, it need more calculation.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 4

Expert Comment

by:andrew_man
ID: 39693019
Yes, it can updated at version 7.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39693029
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
 
LVL 4

Expert Comment

by:andrew_man
ID: 39693032
Yes, tried.  too many calculation.....
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39693073
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
 

Author Comment

by:Bright01
ID: 39693088
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
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 39693098
Ok, I have inserted the code in this file.
Copy-of-Compling-the-Value-List-.xlsm
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39693119
Dear B,

Have you try the version 7?

Andrew
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39693123
I want to know what wrong in the version 7
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39693171
Dear B,

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

Andrew
Compling-the-Value-List-v10.xlsm
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39693203
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
 

Author Closing Comment

by:Bright01
ID: 39693348
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now