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
Bright01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Why attach a workbook with the VBA code protected?

A clear cells macro should clear data not formulas, it's how you write it that will decide.
0
Bright01Author Commented:
Roy,

So sorry!  Let me fix that.
Data-store-and-retrieve.xlsm
0
Bright01Author Commented:
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.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Roy CoxGroup Finance ManagerCommented:
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

On Error Resume Next
Sheet1.Range("A1").CurrentRegion.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0

Open in new window


I haven't heard anything from you for a while, is everythink OK?
0
Roy CoxGroup Finance ManagerCommented:
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 0

Open in new window

0
Bright01Author Commented:
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
0
Roy CoxGroup Finance ManagerCommented:
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
0
Bright01Author Commented:
Big thanks!  Yes.... Death March is just about over..... Now I just have to find a buyer!
0
byundtMechanical EngineerCommented:
Jim,
It wasn't clear what you wanted. I assumed that if the user enters data in I10:N16 it should be copied into each of the three groups in S2:AJ9. And if the user clicks the button, that data should be copied from one of those three groups into I10:N16.

To accomplish this, I needed to revise the worksheet protection to allow the user to select unlocked cells. I also added a Worksheet_Change sub and revised the code in module SelectionNumber.

Detail_Flows additional sub:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rgDest As Range, rgSource As Range, rw As Range
Dim Action As Long, i As Long, j As Long
Set rgSource = Me.Range("I10:N16")
If Not Intersect(rgSource, Target) Is Nothing Then      'Populate each group in S2:AJ9 with data from I10:N16
    Application.EnableEvents = False
    Set rgDest = Me.Range("S2:AJ9")
    rgDest.ClearContents
    Set rgDest = rgDest.Resize(, 6)
    
    For Each rw In rgSource.Rows
        i = i + 1
        If rw.Cells(1, 1) <> "" Then
            For Action = 1 To 3
                j = (Action - 1) * 6
                rgDest.Rows(i).Offset(, j).Value = rw.Value
                rgDest.Rows(i).NumberFormat = rw.NumberFormat
            Next
        End If
    Next
    
    Application.EnableEvents = True
End If
End Sub

Open in new window


SelectionNumber code
Sub ChangingText()
Dim Obj As Shape
Dim Action As Long

With Detail_Flows
    Set Obj = .Shapes(Application.Caller)
               
    If Obj.TextFrame.Characters.Text = "Conservative" Then
        Obj.TextFrame.Characters.Text = "Expected"
        Action = 2
                    
    ElseIf Obj.TextFrame.Characters.Text = "Expected" Then
        Obj.TextFrame.Characters.Text = "Aggressive"
        Action = 3

    ElseIf Obj.TextFrame.Characters.Text = "Aggressive" Then
        Obj.TextFrame.Characters.Text = "Conservative"
        Action = 1
               
    End If
    
    .Range("G4").Value = Action
    PopulateUseCaseRealization Action
End With
End Sub

Sub PopulateUseCaseRealization(Action As Long)
Dim rgSource As Range, rgDest As Range, rw As Range
Dim i As Long

Application.EnableEvents = False
With Detail_Flows
    Set rgDest = .Range("I10:N16")
    rgDest.ClearContents
    Set rgSource = .Range("S2:X9")                          'First group of data in S2:AJ9
    Set rgSource = rgSource.Offset(, 6 * (Action - 1))      'Group chosen by Action
    For Each rw In rgSource.Rows
        i = i + 1
        If rw.Cells(1, 1) <> "" Then
            rgDest.Rows(i).Value = rw.Value
            rgDest.Rows(i).NumberFormat = rw.NumberFormat
        End If
    Next
End With
Application.EnableEvents = True
End Sub

Open in new window

0
Bright01Author Commented:
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
0
byundtMechanical EngineerCommented:
Jim,
I didn't know if you wanted to clear just the data in I10:N16 or also the respective data in S2:AJ9. So I coded both. Delete what you don't need in sub Confirm_BenefitTableReset in module ClearWorksheets.

I changed the Worksheet_Change event sub so data entered in the Input Matrix only updates cells in the respective section of S2:AJ9, and then only if the corresponding value in D10:D16 is not blank.

Brad
Data-store-and-retrieveQ29102408.xlsm
0
Bright01Author Commented:
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
0
byundtMechanical EngineerCommented:
Jim,
I modified the Reset and Clear macros as best I understood your request. In the Reset macro, I guessed that you also wanted the rows in I10:N16 set to 100% if data exists in D10:D16. If not, it is just two statements you need to delete.

I also copied down the formulas for the Use Case number and description. They had previously stopped at the third Use Case. And I changed the formula in S10 so it referenced cell S2 rather than SaaS_Prem_Detailed_Flows!S2, thereby making the formula easier to read and understand.

In your original code, you set the values in S2:AJ9 equal to "1", which is a text value. I changed that to set those cells equal to 1, which is a numeric value displayed as 100%. It may not make any difference here, but it might in some future macro you write--such as if the cell number format were General. The lesson to learn is to avoid, as a matter of practice, setting values equal to numbers stored as text unless you have a good reason to do so.

Brad
Data-Store-and-Retrieve-v3Q29102408.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.