Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Slight Tweak to Selection and Update Program

Posted on 2013-12-03
22
178 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

808 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