Macro to store and retrieve data.
EE Pros,
I have a great little macro that B. Yundt helped me build out and I now need assistance in finishing up the process. The attached xlsx WB/WS shows that when you fire the macro button, the macro cycles through a three step view of "conservative", "expected" and "aggressive" results. The results are a function of % values inputted in the initial screen. What I need is for the values inputted to be posted to the data section to the right. I'm avoiding simply doing it with formulas given I am going to write a "clear cells" macro to refresh the model.
Your assistance is appreciated and "thank you" in advance.
B.
Data-store-and-retrieve.xlsm
I have a great little macro that B. Yundt helped me build out and I now need assistance in finishing up the process. The attached xlsx WB/WS shows that when you fire the macro button, the macro cycles through a three step view of "conservative", "expected" and "aggressive" results. The results are a function of % values inputted in the initial screen. What I need is for the values inputted to be posted to the data section to the right. I'm avoiding simply doing it with formulas given I am going to write a "clear cells" macro to refresh the model.
Your assistance is appreciated and "thank you" in advance.
B.
Data-store-and-retrieve.xlsm
ASKER
ASKER
Stupid me! I should have checked it before sending the original up. Thanks for the catch. So you are saying there is a way to have formulas in specific cells and clear the cells without destroying or eliminating the formulas?
J.
J.
You clear the cells that feed into the formulas.
Which range is it?
Here's an example of what I mean, the code will ignore formulas
I haven't heard anything from you for a while, is everythink OK?
Which range is it?
Here's an example of what I mean, the code will ignore formulas
On Error Resume Next
Sheet1.Range("A1").CurrentRegion.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0I haven't heard anything from you for a while, is everythink OK?
This code will ignore text in a range so will only remove numeric daat that feeds into the formulas
On Error Resume Next
Sheet1.Range("A1").CurrentRegion.SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents
On Error GoTo 0ASKER
Roy,
Sorry for the delay in conversation and interaction. I'm in the process of selling our home of 20 years and it's like a "Death March". I just finished installing 9 new windows, painting outside and inside and I'm on first name basis with the guys at Goodwill and Salvation Army for all the stuff I've taken over there.
Let's catch up soon.
Jim
Sorry for the delay in conversation and interaction. I'm in the process of selling our home of 20 years and it's like a "Death March". I just finished installing 9 new windows, painting outside and inside and I'm on first name basis with the guys at Goodwill and Salvation Army for all the stuff I've taken over there.
Let's catch up soon.
Jim
moving home is a nightmare!
Let me know if you need further help with this one, I've removed the VBA password so I have ccess to the code. I'm heading off soon but I'll check when I get home
Let me know if you need further help with this one, I've removed the VBA password so I have ccess to the code. I'm heading off soon but I'll check when I get home
ASKER
Big thanks! Yes.... Death March is just about over..... Now I just have to find a buyer!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Brad, So close. In testing out your changes I find two slight problems.
1.) The data entered in the Input Matrix should only be posted to the corresponding Data Table Cell associated with the particular benefit level. That is to say for example, when you have the Benefit Level set at "Conservative", when you put in %s in the Matrix, they are only associated with the "Conservative" part of the Data Table.... and so on.
2.) % input should only be allowed if there is text in Column D10:D16 indicating that there is a defined Use Case where Benefits are being calculated ( as opposed to putting in data that is applied to a Use Case that doesn't exist.
That's it! I've attached an update with your code as well as a "CLEAR DATA" button/macro.
Thank you again,
Jim
Data-store-and-retrievev2.xlsm
1.) The data entered in the Input Matrix should only be posted to the corresponding Data Table Cell associated with the particular benefit level. That is to say for example, when you have the Benefit Level set at "Conservative", when you put in %s in the Matrix, they are only associated with the "Conservative" part of the Data Table.... and so on.
2.) % input should only be allowed if there is text in Column D10:D16 indicating that there is a defined Use Case where Benefits are being calculated ( as opposed to putting in data that is applied to a Use Case that doesn't exist.
That's it! I've attached an update with your code as well as a "CLEAR DATA" button/macro.
Thank you again,
Jim
Data-store-and-retrievev2.xlsm
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Brad,
Almost. Two very small tweeks. I've adjusted the "Reset" and the "Clear" Macros. The problem is that when you "Reset" the Data, (which is bac to 100%) it should immediately reflect the change in the math associated with the benefit realization at 100%. SEPARATELY, the only time the cells should be set to 100% are when there is text data (demonstrating that there is a specific Use Case) and it should always be set to 100% for all years as an initial starting point.
Does that make sense? I'd be happy to talk with you about it if it needs clarity.
Updated Model.
Jim
Data-Store-and-Retrieve-v3.xlsm
Almost. Two very small tweeks. I've adjusted the "Reset" and the "Clear" Macros. The problem is that when you "Reset" the Data, (which is bac to 100%) it should immediately reflect the change in the math associated with the benefit realization at 100%. SEPARATELY, the only time the cells should be set to 100% are when there is text data (demonstrating that there is a specific Use Case) and it should always be set to 100% for all years as an initial starting point.
Does that make sense? I'd be happy to talk with you about it if it needs clarity.
Updated Model.
Jim
Data-Store-and-Retrieve-v3.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
PERFECT!!!! Thank you for driving this home. You and Roy are really good at honing down what the requirements are for a particular request and getting it better and better until it's just what needs to be there. When I look at the code and try to figure out what you guys do, I'm constantly amazed at how long it takes to acquire programming language skills.
Thanks again,
J.
Thanks again,
J.
A clear cells macro should clear data not formulas, it's how you write it that will decide.