Solved

# Modify Macro to Perform Additonal Backwards-Functionality

Posted on 2014-07-10
162 Views
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.

``````'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
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
``````
EE-SAFE-FrequencyMacro.xlsm
0
Question by:-Polak

LVL 22

Expert Comment

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

D19 is blank. Did you mean D18 or D20?

Flyster
0

LVL 1

Author Comment

Apologizes, D20
0

LVL 22

Expert Comment

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
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
``````
Flyster
0

LVL 1

Author Comment

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")
``````
0

LVL 1

Author Comment

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

LVL 22

Accepted Solution

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

LVL 1

Author Comment

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

Thanks again.
0

LVL 22

Expert Comment

Thank you. Glad I could help!
0

## Featured Post

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…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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…