Modify Macro to Perform Additonal Backwards-Functionality

Experts, I would request your assistance in modifying the attached macro to performing the following functions:

1.

I would like that if an end-user were to make an entry into daily, monthly, or annual, the value in Cell D25 be the result of Cell D20 multiplied by D25
For example, I entered a daily value of 50, into Daily the current Macro Calculates D25 to equal "18,250". Then D26 is 18,250xD20=47,450

2.

I would like that if an end-user first made an entry into either daily, monthly, or annual saw the result of flight hours as describe by Step 1, BUT then decided he would like to use Annual flight hours as his primary input, as a result, D23:D25 now must adjust based on the new flight hour input divided by D20 and the appropriate frequency either 365days or 12 months
For example, I entered a daily value of 50 into Daily the current Macro Calculates D25 to equal "18250". Then D26 is 18,250xD20=47,450. However, I decide that 47,450 is not what I really would like for annual flight hours to be and manually enter in 50,000 instead. There by changing D23 to (50,000/D19); D24 to (50,000/D19)*30.4 [average number of days in a month]; and D25 to (50,000/D19)*365.

Thanks in advance.

'Dim boolchange As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim dblAnnual As Double
    
    If boolChange Then Exit Sub
    boolChange = False
    dblAnnual = Range("D18").Value
    Select Case Target.Address
        Case "$D$22:$F$22" 'Frequency - but happens if value is deleted
            boolChange = True
            Range("D23").Value = "Waiting for Frequency Selection"
            Range("D24").Value = "Waiting for Frequency Selection"
            Range("D25").Value = dblAnnual
        Case "$D$22" 'Frequency - changing this resets all values to defaults
            boolChange = True
            Select Case Target.Value
                'Case ""
                '    Range("D23").Value = "Waiting for Frequency Selection"
                '    Range("D24").Value = "Waiting for Frequency Selection"
                '    Range("D25").Value = dblAnnual
                Case "Manual Asset"
                    Range("D23").Value = "Continue to Manual Section Below"
                    Range("D24").Value = "Continue to Manual Section Below"
                    Range("D25").Value = "Continue to Manual Section Below"
                Case "Daily"
                    Range("D23").Value = Round(dblAnnual / 365, 0)
                    Range("D24").Value = Round(dblAnnual / 12, 0)
                    Range("D25").Value = Round(dblAnnual / 365, 0) * 365
                Case "Monthly"
                    Range("D23").Value = Round(dblAnnual / 365, 0)
                    Range("D24").Value = Round(dblAnnual / 12, 0)
                    Range("D25").Value = Round(dblAnnual / 12, 0) * 12
                Case "Annual"
                    Range("D23").Value = Round(dblAnnual / 365, 0)
                    Range("D24").Value = Round(dblAnnual / 12, 0)
                    Range("D25").Value = dblAnnual
            End Select
        Case "$D$23" 'Daily amount
            boolChange = True
            Range("D25").Value = Target.Value * 365
            Range("D22").Value = "Daily"
            Range("D24").Value = Round((Target.Value * 365) / 12, 0)
        Case "$D$24" 'Monthly amount
            boolChange = True
            Range("D25").Value = Target.Value * 12
            Range("D22").Value = "Monthly"
            Range("D23").Value = Round((Target.Value * 12) / 365, 0)
        Case "$D$25" 'Annual amount
            boolChange = True
            Range("D22").Value = "Annual"
            Range("D23").Value = Round((Target.Value / 365), 0)
            Range("D24").Value = Round((Target.Value / 12), 0)
            
            'do anything?
    End Select
    boolChange = False
End Sub

Open in new window

EE-SAFE-FrequencyMacro.xlsm
LVL 1
-PolakAsked:
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.

FlysterCommented:
There by changing D23 to (50,000/D19);

D19 is blank. Did you mean D18 or D20?

Flyster
0
-PolakAuthor Commented:
Apologizes, D20
0
FlysterCommented:
Just figured that out. See if this gives you the results you're looking for:

'Dim boolchange As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim dblAnnual As Double
    
    If boolChange Then Exit Sub
    boolChange = False
    dblAnnual = Range("D18").Value
    Select Case Target.Address
        Case "$D$22:$F$22" 'Frequency - but happens if value is deleted
            boolChange = True
            Range("D23").Value = "Waiting for Frequency Selection"
            Range("D24").Value = "Waiting for Frequency Selection"
            Range("D25").Value = dblAnnual
        Case "$D$22" 'Frequency - changing this resets all values to defaults
            boolChange = True
            Select Case Target.Value
                'Case ""
                '    Range("D23").Value = "Waiting for Frequency Selection"
                '    Range("D24").Value = "Waiting for Frequency Selection"
                '    Range("D25").Value = dblAnnual
                Case "Manual Asset"
                    Range("D23").Value = "Continue to Manual Section Below"
                    Range("D24").Value = "Continue to Manual Section Below"
                    Range("D25").Value = "Continue to Manual Section Below"
                Case "Daily"
                    Range("D23").Value = Round(dblAnnual / 365, 0)
                    Range("D24").Value = Round(dblAnnual / 12, 0)
                    Range("D25").Value = Round(dblAnnual / 365, 0) * 365
                Case "Monthly"
                    Range("D23").Value = Round(dblAnnual / 365, 0)
                    Range("D24").Value = Round(dblAnnual / 12, 0)
                    Range("D25").Value = Round(dblAnnual / 12, 0) * 12
                    Range("D26").Value = Range("D20").Value * Range("D25")
                Case "Annual"
                    Range("D23").Value = Round(dblAnnual / 365, 0)
                    Range("D24").Value = Round(dblAnnual / 12, 0)
                    Range("D25").Value = dblAnnual
            End Select
        Case "$D$23" 'Daily amount
            boolChange = True
            Range("D25").Value = Target.Value * 365
            Range("D22").Value = "Daily"
            Range("D24").Value = Round((Target.Value * 365) / 12, 0)
            Range("D26").Value = Range("D20").Value * Range("D25")
        Case "$D$24" 'Monthly amount
            boolChange = True
            Range("D25").Value = Target.Value * 12
            Range("D22").Value = "Monthly"
            Range("D23").Value = Round((Target.Value * 12) / 365, 0)
            Range("D26").Value = Range("D20").Value * Range("D25")
        Case "$D$25" 'Annual amount
            boolChange = True
            Range("D22").Value = "Annual"
            Range("D23").Value = Round((Target.Value / 365), 0)
            Range("D24").Value = Round((Target.Value / 12), 0)
            Range("D26").Value = Range("D20").Value * Range("D25")
        Case "$D$26" 'Annual Flight Hours amount
            boolChange = True
            Range("D22").Value = "Annual Flight Hours"
            Range("D23").Value = Int(Round((Target.Value / Range("D20").Value), 0) / 365)
            Range("D24").Value = Int(Round((Target.Value / Range("D20").Value), 0) / 12)
            Range("D25").Value = Int(Range("D23").Value * 365)
            
            'do anything?
    End Select
    boolChange = False
End Sub

Open in new window

Flyster
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

-PolakAuthor Commented:
Seems to work just fine, quick question though:

Why under the Cases did you only modify "monthly" with
Range("D26").Value = Range("D20").Value * Range("D25")

Open in new window

0
-PolakAuthor Commented:
I've noticed a bug during testing too.

If you simply go to select a frequency first rather than inputting a daily, monthly, or annual amount then Flight Hours remains blank after your selection.
0
FlysterCommented:
Why under the Cases did you only modify "monthly" with
Range("D26").Value = Range("D20").Value * Range("D25")
That line was added to get you the value in cell D26 - Annual Flight Hours. As for the "Bug", that value was not addressed in case $D$22. This will correct that error:
'Dim boolchange As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim dblAnnual As Double
    
    If boolChange Then Exit Sub
    boolChange = False
    dblAnnual = Range("D18").Value
    Select Case Target.Address
        Case "$D$22:$F$22" 'Frequency - but happens if value is deleted
            boolChange = True
            Range("D23").Value = "Waiting for Frequency Selection"
            Range("D24").Value = "Waiting for Frequency Selection"
            Range("D25").Value = dblAnnual
        Case "$D$22" 'Frequency - changing this resets all values to defaults
            boolChange = True
            Select Case Target.Value
                'Case ""
                '    Range("D23").Value = "Waiting for Frequency Selection"
                '    Range("D24").Value = "Waiting for Frequency Selection"
                '    Range("D25").Value = dblAnnual
                Case "Manual Asset"
                    Range("D23").Value = "Continue to Manual Section Below"
                    Range("D24").Value = "Continue to Manual Section Below"
                    Range("D25").Value = "Continue to Manual Section Below"
                    Range("D26").Value = "Make Manual Selection"
                Case "Daily"
                    Range("D23").Value = Round(dblAnnual / 365, 0)
                    Range("D24").Value = Round(dblAnnual / 12, 0)
                    Range("D25").Value = Round(dblAnnual / 365, 0) * 365
                    Range("D26").Value = Range("D20").Value * Range("D25")
                Case "Monthly"
                    Range("D23").Value = Round(dblAnnual / 365, 0)
                    Range("D24").Value = Round(dblAnnual / 12, 0)
                    Range("D25").Value = Round(dblAnnual / 12, 0) * 12
                    Range("D26").Value = Range("D20").Value * Range("D25")
                Case "Annual"
                    Range("D23").Value = Round(dblAnnual / 365, 0)
                    Range("D24").Value = Round(dblAnnual / 12, 0)
                    Range("D25").Value = dblAnnual
                    Range("D26").Value = Range("D20").Value * Range("D25")
            End Select
        Case "$D$23" 'Daily amount
            boolChange = True
            Range("D25").Value = Target.Value * 365
            Range("D22").Value = "Daily"
            Range("D24").Value = Round((Target.Value * 365) / 12, 0)
            Range("D26").Value = Range("D20").Value * Range("D25")
        Case "$D$24" 'Monthly amount
            boolChange = True
            Range("D25").Value = Target.Value * 12
            Range("D22").Value = "Monthly"
            Range("D23").Value = Round((Target.Value * 12) / 365, 0)
            Range("D26").Value = Range("D20").Value * Range("D25")
        Case "$D$25" 'Annual amount
            boolChange = True
            Range("D22").Value = "Annual"
            Range("D23").Value = Round((Target.Value / 365), 0)
            Range("D24").Value = Round((Target.Value / 12), 0)
            Range("D26").Value = Range("D20").Value * Range("D25")
        Case "$D$26" 'Annual Flight Hours amount
            boolChange = True
            Range("D22").Value = "Annual Flight Hours"
            Range("D23").Value = Int(Round((Target.Value / Range("D20").Value), 0) / 365)
            Range("D24").Value = Int(Round((Target.Value / Range("D20").Value), 0) / 12)
            Range("D25").Value = Int(Range("D23").Value * 365)
            
            'do anything?
    End Select
    boolChange = False
End Sub

Open in new window

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
-PolakAuthor Commented:
Gotcha, Thanks, I have a better understanding of the macro now too and I wrote a case for "Annual Flight Hours".

 Case "Annual Flight Hours"
                    Range("D23").Value = Round(dblAnnual / 365, 0)
                    Range("D24").Value = Round(dblAnnual / 12, 0)
                    Range("D25").Value = dblAnnual
                    Range("D26").Value = Round(Range("D20").Value * Range("D25"), 0)

Open in new window


Thanks again.
0
FlysterCommented:
Thank you. Glad I could help!
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.

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.