Solved

Help me Shorten a Procedure

Posted on 2014-10-09
10
145 Views
Last Modified: 2014-10-12
Hi Experts I have the following code running under a Worksheet_Change Sub; I was content with how I was coding it until I hit a procedure too long error. I assume that I can be shortened; however, I'm not certain of how/the syntax.  Your help is appreciated.

             Case "$E$15"
                SortieFHCompat = True
                Range("$F$15").Value = Round(((Target.Value * 365) / 12), 0)
                Range("$G$15").Value = Round((Target.Value * 365), 0)
                Range("$K$15").Value = Round((Target.Value * Range("Q15")), 0)
                Range("$L$15").Value = Round(((Target.Value * Range("Q15")) * 365 / 12), 0)
                Range("$M$15").Value = Round(((Target.Value * Range("Q15")) * 365), 0)
            Case "$E$16"
                SortieFHCompat = True
                Range("$F$16").Value = Round(((Target.Value * 365) / 12), 0)
                Range("$G$16").Value = Round((Target.Value * 365), 0)
                Range("$K$16").Value = Round((Target.Value * Range("Q16")), 0)
                Range("$L$16").Value = Round(((Target.Value * Range("Q16")) * 365 / 12), 0)
                Range("$M$16").Value = Round(((Target.Value * Range("Q16")) * 365), 0)
            Case "$E$17"
                SortieFHCompat = True
                Range("$F$17").Value = Round(((Target.Value * 365) / 12), 0)
                Range("$G$17").Value = Round((Target.Value * 365), 0)
                Range("$K$17").Value = Round((Target.Value * Range("Q17")), 0)
                Range("$L$17").Value = Round(((Target.Value * Range("Q17")) * 365 / 12), 0)
                Range("$M$17").Value = Round(((Target.Value * Range("Q17")) * 365), 0)
            Case "$E$18"
                SortieFHCompat = True
                Range("$F$18").Value = Round(((Target.Value * 365) / 12), 0)
                Range("$G$18").Value = Round((Target.Value * 365), 0)
                Range("$K$18").Value = Round((Target.Value * Range("Q18")), 0)
                Range("$L$18").Value = Round(((Target.Value * Range("Q18")) * 365 / 12), 0)
                Range("$M$18").Value = Round(((Target.Value * Range("Q18")) * 365), 0)
            Case "$E$19"
                SortieFHCompat = True
                Range("$F$19").Value = Round(((Target.Value * 365) / 12), 0)
                Range("$G$19").Value = Round((Target.Value * 365), 0)
                Range("$K$19").Value = Round((Target.Value * Range("Q19")), 0)
                Range("$L$19").Value = Round(((Target.Value * Range("Q19")) * 365 / 12), 0)
                Range("$M$19").Value = Round(((Target.Value * Range("Q19")) * 365), 0)
            Case "$E$20"
                SortieFHCompat = True
                Range("$F$20").Value = Round(((Target.Value * 365) / 12), 0)
                Range("$G$20").Value = Round((Target.Value * 365), 0)
                Range("$K$20").Value = Round((Target.Value * Range("Q20")), 0)
                Range("$L$20").Value = Round(((Target.Value * Range("Q20")) * 365 / 12), 0)
                Range("$M$20").Value = Round(((Target.Value * Range("Q20")) * 365), 0)
            Case "$E$21"
                SortieFHCompat = True
                Range("$F$21").Value = Round(((Target.Value * 365) / 12), 0)
                Range("$G$21").Value = Round((Target.Value * 365), 0)
                Range("$K$21").Value = Round((Target.Value * Range("Q21")), 0)
                Range("$L$21").Value = Round(((Target.Value * Range("Q21")) * 365 / 12), 0)
                Range("$M$21").Value = Round(((Target.Value * Range("Q21")) * 365), 0)
            Case "$E$22"
                SortieFHCompat = True
                Range("$F$22").Value = Round(((Target.Value * 365) / 12), 0)
                Range("$G$22").Value = Round((Target.Value * 365), 0)
                Range("$K$22").Value = Round((Target.Value * Range("Q22")), 0)
                Range("$L$22").Value = Round(((Target.Value * Range("Q22")) * 365 / 12), 0)
                Range("$M$22").Value = Round(((Target.Value * Range("Q22")) * 365), 0)
            Case "$E$23"
                SortieFHCompat = True
                Range("$F$23").Value = Round(((Target.Value * 365) / 12), 0)
                Range("$G$23").Value = Round((Target.Value * 365), 0)
                Range("$K$23").Value = Round((Target.Value * Range("Q23")), 0)
                Range("$L$23").Value = Round(((Target.Value * Range("Q23")) * 365 / 12), 0)
                Range("$M$23").Value = Round(((Target.Value * Range("Q23")) * 365), 0)
            Case "$E$24"
                SortieFHCompat = True
                Range("$F$24").Value = Round(((Target.Value * 365) / 12), 0)
                Range("$G$24").Value = Round((Target.Value * 365), 0)
                Range("$K$24").Value = Round((Target.Value * Range("Q24")), 0)
                Range("$L$24").Value = Round(((Target.Value * Range("Q24")) * 365 / 12), 0)
                Range("$M$24").Value = Round(((Target.Value * Range("Q24")) * 365), 0)
            Case "$E$25"
                SortieFHCompat = True
                Range("$F$25").Value = Round(((Target.Value * 365) / 12), 0)
                Range("$G$25").Value = Round((Target.Value * 365), 0)
                Range("$K$25").Value = Round((Target.Value * Range("Q25")), 0)
                Range("$L$25").Value = Round(((Target.Value * Range("Q25")) * 365 / 12), 0)
                Range("$M$25").Value = Round(((Target.Value * Range("Q25")) * 365), 0)
            Case "$E$26"
                SortieFHCompat = True
                Range("$F$26").Value = Round(((Target.Value * 365) / 12), 0)
                Range("$G$26").Value = Round((Target.Value * 365), 0)
                Range("$K$26").Value = Round((Target.Value * Range("Q26")), 0)
                Range("$L$26").Value = Round(((Target.Value * Range("Q26")) * 365 / 12), 0)
                Range("$M$26").Value = Round(((Target.Value * Range("Q26")) * 365), 0)
            Case "$E$27"
                SortieFHCompat = True
                Range("$F$27").Value = Round(((Target.Value * 365) / 12), 0)
                Range("$G$27").Value = Round((Target.Value * 365), 0)
                Range("$K$27").Value = Round((Target.Value * Range("Q27")), 0)
                Range("$L$27").Value = Round(((Target.Value * Range("Q27")) * 365 / 12), 0)
                Range("$M$27").Value = Round(((Target.Value * Range("Q27")) * 365), 0)
            Case "$E$28"
                SortieFHCompat = True
                Range("$F$28").Value = Round(((Target.Value * 365) / 12), 0)
                Range("$G$28").Value = Round((Target.Value * 365), 0)
                Range("$K$28").Value = Round((Target.Value * Range("Q28")), 0)
                Range("$L$28").Value = Round(((Target.Value * Range("Q28")) * 365 / 12), 0)
                Range("$M$28").Value = Round(((Target.Value * Range("Q28")) * 365), 0)
            Case "$E$29"
                SortieFHCompat = True
                Range("$F$29").Value = Round(((Target.Value * 365) / 12), 0)
                Range("$G$29").Value = Round((Target.Value * 365), 0)
                Range("$K$29").Value = Round((Target.Value * Range("Q29")), 0)
                Range("$L$29").Value = Round(((Target.Value * Range("Q29")) * 365 / 12), 0)
                Range("$M$29").Value = Round(((Target.Value * Range("Q29")) * 365), 0)
            Case "$E$30"
                SortieFHCompat = True
                Range("$F$30").Value = Round(((Target.Value * 365) / 12), 0)
                Range("$G$30").Value = Round((Target.Value * 365), 0)
                Range("$K$30").Value = Round((Target.Value * Range("Q30")), 0)
                Range("$L$30").Value = Round(((Target.Value * Range("Q30")) * 365 / 12), 0)
                Range("$M$30").Value = Round(((Target.Value * Range("Q30")) * 365), 0)
             Case "$E$31"
                SortieFHCompat = True
                Range("$F$31").Value = Round(((Target.Value * 365) / 12), 0)
                Range("$G$31").Value = Round((Target.Value * 365), 0)
                Range("$K$31").Value = Round((Target.Value * Range("Q31")), 0)
                Range("$L$31").Value = Round(((Target.Value * Range("Q31")) * 365 / 12), 0)
                Range("$M$31").Value = Round(((Target.Value * Range("Q31")) * 365), 0)
             Case "$E$32"
                SortieFHCompat = True
                Range("$F$32").Value = Round(((Target.Value * 365) / 12), 0)
                Range("$G$32").Value = Round((Target.Value * 365), 0)
                Range("$K$32").Value = Round((Target.Value * Range("Q32")), 0)
                Range("$L$32").Value = Round(((Target.Value * Range("Q32")) * 365 / 12), 0)
                Range("$M$32").Value = Round(((Target.Value * Range("Q32")) * 365), 0)
             Case "$E$33"
                SortieFHCompat = True
                Range("$F$33").Value = Round(((Target.Value * 365) / 12), 0)
                Range("$G$33").Value = Round((Target.Value * 365), 0)
                Range("$K$33").Value = Round((Target.Value * Range("Q33")), 0)
                Range("$L$33").Value = Round(((Target.Value * Range("Q33")) * 365 / 12), 0)
                Range("$M$33").Value = Round(((Target.Value * Range("Q33")) * 365), 0)
            Case "$E$42"
                SortieFHCompat = True
                Range("$F$42").Value = Round(((Target.Value * 365) / 12), 0)
                Range("$G$42").Value = Round((Target.Value * 365), 0)
                Range("$K$42").Value = Round((Target.Value * Range("Q42")), 0)
                Range("$L$42").Value = Round(((Target.Value * Range("Q42")) * 365 / 12), 0)
                Range("$M$42").Value = Round(((Target.Value * Range("Q42")) * 365), 0)
            Case "$E$43"
                SortieFHCompat = True
                Range("$F$43").Value = Round(((Target.Value * 365) / 12), 0)
                Range("$G$43").Value = Round((Target.Value * 365), 0)
                Range("$K$43").Value = Round((Target.Value * Range("Q43")), 0)
                Range("$L$43").Value = Round(((Target.Value * Range("Q43")) * 365 / 12), 0)
                Range("$M$43").Value = Round(((Target.Value * Range("Q43")) * 365), 0)
            Case "$E$44"
                SortieFHCompat = True
                Range("$F$44").Value = Round(((Target.Value * 365) / 12), 0)
                Range("$G$44").Value = Round((Target.Value * 365), 0)
                Range("$K$44").Value = Round((Target.Value * Range("Q44")), 0)
                Range("$L$44").Value = Round(((Target.Value * Range("Q44")) * 365 / 12), 0)
                Range("$M$44").Value = Round(((Target.Value * Range("Q44")) * 365), 0)
            Case "$E$45"
                SortieFHCompat = True
                Range("$F$45").Value = Round(((Target.Value * 365) / 12), 0)
                Range("$G$45").Value = Round((Target.Value * 365), 0)
                Range("$K$45").Value = Round((Target.Value * Range("Q45")), 0)
                Range("$L$45").Value = Round(((Target.Value * Range("Q45")) * 365 / 12), 0)
                Range("$M$45").Value = Round(((Target.Value * Range("Q45")) * 365), 0)
'Planning MONTHLY Sorties in this section
            Case "$F$15"
                SortieFHCompat = True
                Range("$E$15").Value = Round(((Target.Value * 12) / 365), 0)
                Range("$G$15").Value = Round((Target.Value * 12), 0)
                Range("$K$15").Value = Round(((Target.Value * Range("Q15")) * 12 / 365), 0)
                Range("$L$15").Value = Round((Target.Value * Range("Q15")), 0)
                Range("$M$15").Value = Round(((Target.Value * Range("Q15")) * 12), 0)
            Case "$F$16"
                SortieFHCompat = True
                Range("$E$16").Value = Round(((Target.Value * 12) / 365), 0)
                Range("$G$16").Value = Round((Target.Value * 12), 0)
                Range("$K$16").Value = Round(((Target.Value * Range("Q16")) * 12 / 365), 0)
                Range("$L$16").Value = Round((Target.Value * Range("Q16")), 0)
                Range("$M$16").Value = Round(((Target.Value * Range("Q16")) * 12), 0)
            Case "$F$17"
                SortieFHCompat = True
                Range("$E$17").Value = Round(((Target.Value * 12) / 365), 0)
                Range("$G$17").Value = Round((Target.Value * 12), 0)
                Range("$K$17").Value = Round(((Target.Value * Range("Q17")) * 12 / 365), 0)
                Range("$L$17").Value = Round((Target.Value * Range("Q17")), 0)
                Range("$M$17").Value = Round(((Target.Value * Range("Q17")) * 12), 0)
             Case "$F$18"
                SortieFHCompat = True
                Range("$E$18").Value = Round(((Target.Value * 12) / 365), 0)
                Range("$G$18").Value = Round((Target.Value * 12), 0)
                Range("$K$18").Value = Round(((Target.Value * Range("Q18")) * 12 / 365), 0)
                Range("$L$18").Value = Round((Target.Value * Range("Q18")), 0)
                Range("$M$18").Value = Round(((Target.Value * Range("Q18")) * 12), 0)
             Case "$F$19"
                SortieFHCompat = True
                Range("$E$19").Value = Round(((Target.Value * 12) / 365), 0)
                Range("$G$19").Value = Round((Target.Value * 12), 0)
                Range("$K$19").Value = Round(((Target.Value * Range("Q19")) * 12 / 365), 0)
                Range("$L$19").Value = Round((Target.Value * Range("Q19")), 0)
                Range("$M$19").Value = Round(((Target.Value * Range("Q19")) * 12), 0)
             Case "$F$20"
                SortieFHCompat = True
                Range("$E$20").Value = Round(((Target.Value * 12) / 365), 0)
                Range("$G$20").Value = Round((Target.Value * 12), 0)
                Range("$K$20").Value = Round(((Target.Value * Range("Q20")) * 12 / 365), 0)
                Range("$L$20").Value = Round((Target.Value * Range("Q20")), 0)
                Range("$M$20").Value = Round(((Target.Value * Range("Q20")) * 12), 0)
             Case "$F$21"
                SortieFHCompat = True
                Range("$E$21").Value = Round(((Target.Value * 12) / 365), 0)
                Range("$G$21").Value = Round((Target.Value * 12), 0)
                Range("$K$21").Value = Round(((Target.Value * Range("Q21")) * 12 / 365), 0)
                Range("$L$21").Value = Round((Target.Value * Range("Q21")), 0)
                Range("$M$21").Value = Round(((Target.Value * Range("Q21")) * 12), 0)
             Case "$F$22"
                SortieFHCompat = True
                Range("$E$22").Value = Round(((Target.Value * 12) / 365), 0)
                Range("$G$22").Value = Round((Target.Value * 12), 0)
                Range("$K$22").Value = Round(((Target.Value * Range("Q22")) * 12 / 365), 0)
                Range("$L$22").Value = Round((Target.Value * Range("Q22")), 0)
                Range("$M$22").Value = Round(((Target.Value * Range("Q22")) * 12), 0)
             Case "$F$23"
                SortieFHCompat = True
                Range("$E$23").Value = Round(((Target.Value * 12) / 365), 0)
                Range("$G$23").Value = Round((Target.Value * 12), 0)
                Range("$K$23").Value = Round(((Target.Value * Range("Q23")) * 12 / 365), 0)
                Range("$L$23").Value = Round((Target.Value * Range("Q23")), 0)
                Range("$M$23").Value = Round(((Target.Value * Range("Q23")) * 12), 0)
             Case "$F$24"
                SortieFHCompat = True
                Range("$E$24").Value = Round(((Target.Value * 12) / 365), 0)
                Range("$G$24").Value = Round((Target.Value * 12), 0)
                Range("$K$24").Value = Round(((Target.Value * Range("Q24")) * 12 / 365), 0)
                Range("$L$24").Value = Round((Target.Value * Range("Q24")), 0)
                Range("$M$24").Value = Round(((Target.Value * Range("Q24")) * 12), 0)
             Case "$F$25"
                SortieFHCompat = True
                Range("$E$25").Value = Round(((Target.Value * 12) / 365), 0)
                Range("$G$25").Value = Round((Target.Value * 12), 0)
                Range("$K$25").Value = Round(((Target.Value * Range("Q25")) * 12 / 365), 0)
                Range("$L$25").Value = Round((Target.Value * Range("Q25")), 0)
                Range("$M$25").Value = Round(((Target.Value * Range("Q25")) * 12), 0)
             Case "$F$26"
                SortieFHCompat = True
                Range("$E$26").Value = Round(((Target.Value * 12) / 365), 0)
                Range("$G$26").Value = Round((Target.Value * 12), 0)
                Range("$K$26").Value = Round(((Target.Value * Range("Q26")) * 12 / 365), 0)
                Range("$L$26").Value = Round((Target.Value * Range("Q26")), 0)
                Range("$M$26").Value = Round(((Target.Value * Range("Q26")) * 12), 0)
             Case "$F$27"
                SortieFHCompat = True
                Range("$E$27").Value = Round(((Target.Value * 12) / 365), 0)
                Range("$G$27").Value = Round((Target.Value * 12), 0)
                Range("$K$27").Value = Round(((Target.Value * Range("Q27")) * 12 / 365), 0)
                Range("$L$27").Value = Round((Target.Value * Range("Q27")), 0)
                Range("$M$27").Value = Round(((Target.Value * Range("Q27")) * 12), 0)
             Case "$F$28"
                SortieFHCompat = True
                Range("$E$28").Value = Round(((Target.Value * 12) / 365), 0)
                Range("$G$28").Value = Round((Target.Value * 12), 0)
                Range("$K$28").Value = Round(((Target.Value * Range("Q28")) * 12 / 365), 0)
                Range("$L$28").Value = Round((Target.Value * Range("Q28")), 0)
                Range("$M$28").Value = Round(((Target.Value * Range("Q28")) * 12), 0)
             Case "$F$29"
                SortieFHCompat = True
                Range("$E$29").Value = Round(((Target.Value * 12) / 365), 0)
                Range("$G$29").Value = Round((Target.Value * 12), 0)
                Range("$K$29").Value = Round(((Target.Value * Range("Q29")) * 12 / 365), 0)
                Range("$L$29").Value = Round((Target.Value * Range("Q29")), 0)
                Range("$M$29").Value = Round(((Target.Value * Range("Q29")) * 12), 0)
             Case "$F$30"
                SortieFHCompat = True
                Range("$E$30").Value = Round(((Target.Value * 12) / 365), 0)
                Range("$G$30").Value = Round((Target.Value * 12), 0)
                Range("$K$30").Value = Round(((Target.Value * Range("Q30")) * 12 / 365), 0)
                Range("$L$30").Value = Round((Target.Value * Range("Q30")), 0)
                Range("$M$30").Value = Round(((Target.Value * Range("Q30")) * 12), 0)
             Case "$F$31"
                SortieFHCompat = True
                Range("$E$31").Value = Round(((Target.Value * 12) / 365), 0)
                Range("$G$31").Value = Round((Target.Value * 12), 0)
                Range("$K$31").Value = Round(((Target.Value * Range("Q31")) * 12 / 365), 0)
                Range("$L$31").Value = Round((Target.Value * Range("Q31")), 0)
                Range("$M$31").Value = Round(((Target.Value * Range("Q31")) * 12), 0)
              Case "$F$32"
                SortieFHCompat = True
                Range("$E$32").Value = Round(((Target.Value * 12) / 365), 0)
                Range("$G$32").Value = Round((Target.Value * 12), 0)
                Range("$K$32").Value = Round(((Target.Value * Range("Q32")) * 12 / 365), 0)
                Range("$L$32").Value = Round((Target.Value * Range("Q32")), 0)
                Range("$M$32").Value = Round(((Target.Value * Range("Q32")) * 12), 0)
              Case "$F$33"
                SortieFHCompat = True
                Range("$E$33").Value = Round(((Target.Value * 12) / 365), 0)
                Range("$G$33").Value = Round((Target.Value * 12), 0)
                Range("$K$33").Value = Round(((Target.Value * Range("Q33")) * 12 / 365), 0)
                Range("$L$33").Value = Round((Target.Value * Range("Q33")), 0)
                Range("$M$33").Value = Round(((Target.Value * Range("Q33")) * 12), 0)
              Case "$F$42"
                SortieFHCompat = True
                Range("$E$42").Value = Round(((Target.Value * 12) / 365), 0)
                Range("$G$42").Value = Round((Target.Value * 12), 0)
                Range("$K$42").Value = Round(((Target.Value * Range("Q42")) * 12 / 365), 0)
                Range("$L$42").Value = Round((Target.Value * Range("Q42")), 0)
                Range("$M$42").Value = Round(((Target.Value * Range("Q42")) * 12), 0)
              Case "$F$43"
                SortieFHCompat = True
                Range("$E$43").Value = Round(((Target.Value * 12) / 365), 0)
                Range("$G$43").Value = Round((Target.Value * 12), 0)
                Range("$K$43").Value = Round(((Target.Value * Range("Q43")) * 12 / 365), 0)
                Range("$L$43").Value = Round((Target.Value * Range("Q43")), 0)
                Range("$M$43").Value = Round(((Target.Value * Range("Q43")) * 12), 0)
              Case "$F$44"
                SortieFHCompat = True
                Range("$E$44").Value = Round(((Target.Value * 12) / 365), 0)
                Range("$G$44").Value = Round((Target.Value * 12), 0)
                Range("$K$44").Value = Round(((Target.Value * Range("Q44")) * 12 / 365), 0)
                Range("$L$44").Value = Round((Target.Value * Range("Q44")), 0)
                Range("$M$44").Value = Round(((Target.Value * Range("Q44")) * 12), 0)
              Case "$F$45"
                SortieFHCompat = True
                Range("$E$45").Value = Round(((Target.Value * 12) / 365), 0)
                Range("$G$45").Value = Round((Target.Value * 12), 0)
                Range("$K$45").Value = Round(((Target.Value * Range("Q45")) * 12 / 365), 0)
                Range("$L$45").Value = Round((Target.Value * Range("Q45")), 0)
                Range("$M$45").Value = Round(((Target.Value * Range("Q45")) * 12), 0)
'Planning YEARLY Sorties in this section
              Case "$G$15"
                SortieFHCompat = True
                Range("$E$15").Value = Round(((Target.Value) / 365), 0)
                Range("$F$15").Value = Round((Target.Value / 12), 0)
                Range("$K$15").Value = Round(((Target.Value * Range("Q15")) / 365), 0)
                Range("$L$15").Value = Round(((Target.Value * Range("Q15")) / 12), 0)
                Range("$M$15").Value = Round((Target.Value * Range("Q15")), 0)
              Case "$G$16"
                SortieFHCompat = True
                Range("$E$16").Value = Round(((Target.Value) / 365), 0)
                Range("$F$16").Value = Round((Target.Value / 12), 0)
                Range("$K$16").Value = Round(((Target.Value * Range("Q16")) / 365), 0)
                Range("$L$16").Value = Round(((Target.Value * Range("Q16")) / 12), 0)
                Range("$M$16").Value = Round((Target.Value * Range("Q16")), 0)
              Case "$G$17"
                SortieFHCompat = True
                Range("$E$17").Value = Round(((Target.Value) / 365), 0)
                Range("$F$17").Value = Round((Target.Value / 12), 0)
                Range("$K$17").Value = Round(((Target.Value * Range("Q17")) / 365), 0)
                Range("$L$17").Value = Round(((Target.Value * Range("Q17")) / 12), 0)
                Range("$M$17").Value = Round((Target.Value * Range("Q17")), 0)
              Case "$G$18"
                SortieFHCompat = True
                Range("$E$18").Value = Round(((Target.Value) / 365), 0)
                Range("$F$18").Value = Round((Target.Value / 12), 0)
                Range("$K$18").Value = Round(((Target.Value * Range("Q18")) / 365), 0)
                Range("$L$18").Value = Round(((Target.Value * Range("Q18")) / 12), 0)
                Range("$M$18").Value = Round((Target.Value * Range("Q18")), 0)
              Case "$G$19"
                SortieFHCompat = True
                Range("$E$19").Value = Round(((Target.Value) / 365), 0)
                Range("$F$19").Value = Round((Target.Value / 12), 0)
                Range("$K$19").Value = Round(((Target.Value * Range("Q19")) / 365), 0)
                Range("$L$19").Value = Round(((Target.Value * Range("Q19")) / 12), 0)
                Range("$M$19").Value = Round((Target.Value * Range("Q19")), 0)
              Case "$G$20"
                SortieFHCompat = True
                Range("$E$20").Value = Round(((Target.Value) / 365), 0)
                Range("$F$20").Value = Round((Target.Value / 12), 0)
                Range("$K$20").Value = Round(((Target.Value * Range("Q20")) / 365), 0)
                Range("$L$20").Value = Round(((Target.Value * Range("Q20")) / 12), 0)
                Range("$M$20").Value = Round((Target.Value * Range("Q20")), 0)
              Case "$G$21"
                SortieFHCompat = True
                Range("$E$21").Value = Round(((Target.Value) / 365), 0)
                Range("$F$21").Value = Round((Target.Value / 12), 0)
                Range("$K$21").Value = Round(((Target.Value * Range("Q21")) / 365), 0)
                Range("$L$21").Value = Round(((Target.Value * Range("Q21")) / 12), 0)
                Range("$M$21").Value = Round((Target.Value * Range("Q21")), 0)
              Case "$G$22"
                SortieFHCompat = True
                Range("$E$22").Value = Round(((Target.Value) / 365), 0)
                Range("$F$22").Value = Round((Target.Value / 12), 0)
                Range("$K$22").Value = Round(((Target.Value * Range("Q22")) / 365), 0)
                Range("$L$22").Value = Round(((Target.Value * Range("Q22")) / 12), 0)
                Range("$M$22").Value = Round((Target.Value * Range("Q22")), 0)
              Case "$G$23"
                SortieFHCompat = True
                Range("$E$23").Value = Round(((Target.Value) / 365), 0)
                Range("$F$23").Value = Round((Target.Value / 12), 0)
                Range("$K$23").Value = Round(((Target.Value * Range("Q23")) / 365), 0)
                Range("$L$23").Value = Round(((Target.Value * Range("Q23")) / 12), 0)
                Range("$M$23").Value = Round((Target.Value * Range("Q23")), 0)
              Case "$G$24"
                SortieFHCompat = True
                Range("$E$24").Value = Round(((Target.Value) / 365), 0)
                Range("$F$24").Value = Round((Target.Value / 12), 0)
                Range("$K$24").Value = Round(((Target.Value * Range("Q24")) / 365), 0)
                Range("$L$24").Value = Round(((Target.Value * Range("Q24")) / 12), 0)
                Range("$M$24").Value = Round((Target.Value * Range("Q24")), 0)
              Case "$G$25"
                SortieFHCompat = True
                Range("$E$25").Value = Round(((Target.Value) / 365), 0)
                Range("$F$25").Value = Round((Target.Value / 12), 0)
                Range("$K$25").Value = Round(((Target.Value * Range("Q25")) / 365), 0)
                Range("$L$25").Value = Round(((Target.Value * Range("Q25")) / 12), 0)
                Range("$M$25").Value = Round((Target.Value * Range("Q25")), 0)
              Case "$G$26"
                SortieFHCompat = True
                Range("$E$26").Value = Round(((Target.Value) / 365), 0)
                Range("$F$26").Value = Round((Target.Value / 12), 0)
                Range("$K$26").Value = Round(((Target.Value * Range("Q26")) / 365), 0)
                Range("$L$26").Value = Round(((Target.Value * Range("Q26")) / 12), 0)
                Range("$M$26").Value = Round((Target.Value * Range("Q26")), 0)
              Case "$G$27"
                SortieFHCompat = True
                Range("$E$27").Value = Round(((Target.Value) / 365), 0)
                Range("$F$27").Value = Round((Target.Value / 12), 0)
                Range("$K$27").Value = Round(((Target.Value * Range("Q27")) / 365), 0)
                Range("$L$27").Value = Round(((Target.Value * Range("Q27")) / 12), 0)
                Range("$M$27").Value = Round((Target.Value * Range("Q27")), 0)
              Case "$G$28"
                SortieFHCompat = True
                Range("$E$28").Value = Round(((Target.Value) / 365), 0)
                Range("$F$28").Value = Round((Target.Value / 12), 0)
                Range("$K$28").Value = Round(((Target.Value * Range("Q28")) / 365), 0)
                Range("$L$28").Value = Round(((Target.Value * Range("Q28")) / 12), 0)
                Range("$M$28").Value = Round((Target.Value * Range("Q28")), 0)
              Case "$G$29"
                SortieFHCompat = True
                Range("$E$29").Value = Round(((Target.Value) / 365), 0)
                Range("$F$29").Value = Round((Target.Value / 12), 0)
                Range("$K$29").Value = Round(((Target.Value * Range("Q29")) / 365), 0)
                Range("$L$29").Value = Round(((Target.Value * Range("Q29")) / 12), 0)
                Range("$M$29").Value = Round((Target.Value * Range("Q29")), 0)
              Case "$G$30"
                SortieFHCompat = True
                Range("$E$30").Value = Round(((Target.Value) / 365), 0)
                Range("$F$30").Value = Round((Target.Value / 12), 0)
                Range("$K$30").Value = Round(((Target.Value * Range("Q30")) / 365), 0)
                Range("$L$30").Value = Round(((Target.Value * Range("Q30")) / 12), 0)
                Range("$M$30").Value = Round((Target.Value * Range("Q30")), 0)
              Case "$G$31"
                SortieFHCompat = True
                Range("$E$31").Value = Round(((Target.Value) / 365), 0)
                Range("$F$31").Value = Round((Target.Value / 12), 0)
                Range("$K$31").Value = Round(((Target.Value * Range("Q31")) / 365), 0)
                Range("$L$31").Value = Round(((Target.Value * Range("Q31")) / 12), 0)
                Range("$M$31").Value = Round((Target.Value * Range("Q31")), 0)
              Case "$G$32"
                SortieFHCompat = True
                Range("$E$32").Value = Round(((Target.Value) / 365), 0)
                Range("$F$32").Value = Round((Target.Value / 12), 0)
                Range("$K$32").Value = Round(((Target.Value * Range("Q32")) / 365), 0)
                Range("$L$32").Value = Round(((Target.Value * Range("Q32")) / 12), 0)
                Range("$M$32").Value = Round((Target.Value * Range("Q32")), 0)
              Case "$G$33"
                SortieFHCompat = True
                Range("$E$33").Value = Round(((Target.Value) / 365), 0)
                Range("$F$33").Value = Round((Target.Value / 12), 0)
                Range("$K$33").Value = Round(((Target.Value * Range("Q33")) / 365), 0)
                Range("$L$33").Value = Round(((Target.Value * Range("Q33")) / 12), 0)
                Range("$M$33").Value = Round((Target.Value * Range("Q33")), 0)
              Case "$G$42"
                SortieFHCompat = True
                Range("$E$42").Value = Round(((Target.Value) / 365), 0)
                Range("$F$42").Value = Round((Target.Value / 12), 0)
                Range("$K$42").Value = Round(((Target.Value * Range("Q42")) / 365), 0)
                Range("$L$42").Value = Round(((Target.Value * Range("Q42")) / 12), 0)
                Range("$M$42").Value = Round((Target.Value * Range("Q42")), 0)
              Case "$G$43"
                SortieFHCompat = True
                Range("$E$43").Value = Round(((Target.Value) / 365), 0)
                Range("$F$43").Value = Round((Target.Value / 12), 0)
                Range("$K$43").Value = Round(((Target.Value * Range("Q43")) / 365), 0)
                Range("$L$43").Value = Round(((Target.Value * Range("Q43")) / 12), 0)
                Range("$M$43").Value = Round((Target.Value * Range("Q43")), 0)
              Case "$G$44"
                SortieFHCompat = True
                Range("$E$44").Value = Round(((Target.Value) / 365), 0)
                Range("$F$44").Value = Round((Target.Value / 12), 0)
                Range("$K$44").Value = Round(((Target.Value * Range("Q44")) / 365), 0)
                Range("$L$44").Value = Round(((Target.Value * Range("Q44")) / 12), 0)
                Range("$M$44").Value = Round((Target.Value * Range("Q44")), 0)
              Case "$G$45"
                SortieFHCompat = True
                Range("$E$45").Value = Round(((Target.Value) / 365), 0)
                Range("$F$45").Value = Round((Target.Value / 12), 0)
                Range("$K$45").Value = Round(((Target.Value * Range("Q45")) / 365), 0)
                Range("$L$45").Value = Round(((Target.Value * Range("Q45")) / 12), 0)
                Range("$M$45").Value = Round((Target.Value * Range("Q45")), 0)
'Planning DAILY FH in this section
              Case "$K$15"
                SortieFHCompat = True
                Range("$E$15").Value = Round(((Target.Value) / Range("Q15")), 0)
                Range("$F$15").Value = Round(((Target.Value) / Range("Q15") * 12), 0)
                Range("$G$15").Value = Round(((Target.Value) / Range("Q15") * 365), 0)
                Range("$L$15").Value = Round((Target.Value * 12), 0)
                Range("$M$15").Value = Round((Target.Value * 365), 0)
              Case "$K$16"
                SortieFHCompat = True
                Range("$E$16").Value = Round(((Target.Value) / Range("Q16")), 0)
                Range("$F$16").Value = Round(((Target.Value) / Range("Q16") * 12), 0)
                Range("$G$16").Value = Round(((Target.Value) / Range("Q16") * 365), 0)
                Range("$L$16").Value = Round((Target.Value * 12), 0)
                Range("$M$16").Value = Round((Target.Value * 365), 0)
              Case "$K$17"
                SortieFHCompat = True
                Range("$E$17").Value = Round(((Target.Value) / Range("Q17")), 0)
                Range("$F$17").Value = Round(((Target.Value) / Range("Q17") * 12), 0)
                Range("$G$17").Value = Round(((Target.Value) / Range("Q17") * 365), 0)
                Range("$L$17").Value = Round((Target.Value * 12), 0)
                Range("$M$17").Value = Round((Target.Value * 365), 0)
              Case "$K$18"
                SortieFHCompat = True
                Range("$E$18").Value = Round(((Target.Value) / Range("Q18")), 0)
                Range("$F$18").Value = Round(((Target.Value) / Range("Q18") * 12), 0)
                Range("$G$18").Value = Round(((Target.Value) / Range("Q18") * 365), 0)
                Range("$L$18").Value = Round((Target.Value * 12), 0)
                Range("$M$18").Value = Round((Target.Value * 365), 0)
              Case "$K$19"
                SortieFHCompat = True
                Range("$E$19").Value = Round(((Target.Value) / Range("Q19")), 0)
                Range("$F$19").Value = Round(((Target.Value) / Range("Q19") * 12), 0)
                Range("$G$19").Value = Round(((Target.Value) / Range("Q19") * 365), 0)
                Range("$L$19").Value = Round((Target.Value * 12), 0)
                Range("$M$19").Value = Round((Target.Value * 365), 0)
              Case "$K$20"
                SortieFHCompat = True
                Range("$E$20").Value = Round(((Target.Value) / Range("Q20")), 0)
                Range("$F$20").Value = Round(((Target.Value) / Range("Q20") * 12), 0)
                Range("$G$20").Value = Round(((Target.Value) / Range("Q20") * 365), 0)
                Range("$L$20").Value = Round((Target.Value * 12), 0)
                Range("$M$20").Value = Round((Target.Value * 365), 0)
              Case "$K$21"
                SortieFHCompat = True
                Range("$E$21").Value = Round(((Target.Value) / Range("Q21")), 0)
                Range("$F$21").Value = Round(((Target.Value) / Range("Q21") * 12), 0)
                Range("$G$21").Value = Round(((Target.Value) / Range("Q21") * 365), 0)
                Range("$L$21").Value = Round((Target.Value * 12), 0)
                Range("$M$21").Value = Round((Target.Value * 365), 0)
              Case "$K$22"
                SortieFHCompat = True
                Range("$E$22").Value = Round(((Target.Value) / Range("Q22")), 0)
                Range("$F$22").Value = Round(((Target.Value) / Range("Q22") * 12), 0)
                Range("$G$22").Value = Round(((Target.Value) / Range("Q22") * 365), 0)
                Range("$L$22").Value = Round((Target.Value * 12), 0)
                Range("$M$22").Value = Round((Target.Value * 365), 0)
              Case "$K$23"
                SortieFHCompat = True
                Range("$E$23").Value = Round(((Target.Value) / Range("Q23")), 0)
                Range("$F$23").Value = Round(((Target.Value) / Range("Q23") * 12), 0)
                Range("$G$23").Value = Round(((Target.Value) / Range("Q23") * 365), 0)
                Range("$L$23").Value = Round((Target.Value * 12), 0)
                Range("$M$23").Value = Round((Target.Value * 365), 0)
              Case "$K$24"
                SortieFHCompat = True
                Range("$E$24").Value = Round(((Target.Value) / Range("Q24")), 0)
                Range("$F$24").Value = Round(((Target.Value) / Range("Q24") * 12), 0)
                Range("$G$24").Value = Round(((Target.Value) / Range("Q24") * 365), 0)
                Range("$L$24").Value = Round((Target.Value * 12), 0)
                Range("$M$24").Value = Round((Target.Value * 365), 0)
              Case "$K$25"
                SortieFHCompat = True
                Range("$E$25").Value = Round(((Target.Value) / Range("Q25")), 0)
                Range("$F$25").Value = Round(((Target.Value) / Range("Q25") * 12), 0)
                Range("$G$25").Value = Round(((Target.Value) / Range("Q25") * 365), 0)
                Range("$L$25").Value = Round((Target.Value * 12), 0)
                Range("$M$25").Value = Round((Target.Value * 365), 0)
              Case "$K$26"
                SortieFHCompat = True
                Range("$E$26").Value = Round(((Target.Value) / Range("Q26")), 0)
                Range("$F$26").Value = Round(((Target.Value) / Range("Q26") * 12), 0)
                Range("$G$26").Value = Round(((Target.Value) / Range("Q26") * 365), 0)
                Range("$L$26").Value = Round((Target.Value * 12), 0)
                Range("$M$26").Value = Round((Target.Value * 365), 0)
              Case "$K$27"
                SortieFHCompat = True
                Range("$E$27").Value = Round(((Target.Value) / Range("Q27")), 0)
                Range("$F$27").Value = Round(((Target.Value) / Range("Q27") * 12), 0)
                Range("$G$27").Value = Round(((Target.Value) / Range("Q27") * 365), 0)
                Range("$L$27").Value = Round((Target.Value * 12), 0)
                Range("$M$27").Value = Round((Target.Value * 365), 0)
              Case "$K$28"
                SortieFHCompat = True
                Range("$E$28").Value = Round(((Target.Value) / Range("Q28")), 0)
                Range("$F$28").Value = Round(((Target.Value) / Range("Q28") * 12), 0)
                Range("$G$28").Value = Round(((Target.Value) / Range("Q28") * 365), 0)
                Range("$L$28").Value = Round((Target.Value * 12), 0)
                Range("$M$28").Value = Round((Target.Value * 365), 0)
              Case "$K$29"
                SortieFHCompat = True
                Range("$E$29").Value = Round(((Target.Value) / Range("Q29")), 0)
                Range("$F$29").Value = Round(((Target.Value) / Range("Q29") * 12), 0)
                Range("$G$29").Value = Round(((Target.Value) / Range("Q29") * 365), 0)
                Range("$L$29").Value = Round((Target.Value * 12), 0)
                Range("$M$29").Value = Round((Target.Value * 365), 0)
              Case "$K$30"
                SortieFHCompat = True
                Range("$E$30").Value = Round(((Target.Value) / Range("Q30")), 0)
                Range("$F$30").Value = Round(((Target.Value) / Range("Q30") * 12), 0)
                Range("$G$30").Value = Round(((Target.Value) / Range("Q30") * 365), 0)
                Range("$L$30").Value = Round((Target.Value * 12), 0)
                Range("$M$30").Value = Round((Target.Value * 365), 0)
              Case "$K$31"
                SortieFHCompat = True
                Range("$E$31").Value = Round(((Target.Value) / Range("Q31")), 0)
                Range("$F$31").Value = Round(((Target.Value) / Range("Q31") * 12), 0)
                Range("$G$31").Value = Round(((Target.Value) / Range("Q31") * 365), 0)
                Range("$L$31").Value = Round((Target.Value * 12), 0)
                Range("$M$31").Value = Round((Target.Value * 365), 0)
              Case "$K$32"
                SortieFHCompat = True
                Range("$E$32").Value = Round(((Target.Value) / Range("Q32")), 0)
                Range("$F$32").Value = Round(((Target.Value) / Range("Q32") * 12), 0)
                Range("$G$32").Value = Round(((Target.Value) / Range("Q32") * 365), 0)
                Range("$L$32").Value = Round((Target.Value * 12), 0)
                Range("$M$32").Value = Round((Target.Value * 365), 0)
              Case "$K$33"
                SortieFHCompat = True
                Range("$E$33").Value = Round(((Target.Value) / Range("Q33")), 0)
                Range("$F$33").Value = Round(((Target.Value) / Range("Q33") * 12), 0)
                Range("$G$33").Value = Round(((Target.Value) / Range("Q33") * 365), 0)
                Range("$L$33").Value = Round((Target.Value * 12), 0)
                Range("$M$33").Value = Round((Target.Value * 365), 0)
              Case "$K$42"
                SortieFHCompat = True
                Range("$E$42").Value = Round(((Target.Value) / Range("Q42")), 0)
                Range("$F$42").Value = Round(((Target.Value) / Range("Q42") * 12), 0)
                Range("$G$42").Value = Round(((Target.Value) / Range("Q42") * 365), 0)
                Range("$L$42").Value = Round((Target.Value * 12), 0)
                Range("$M$42").Value = Round((Target.Value * 365), 0)
              Case "$K$43"
                SortieFHCompat = True
                Range("$E$43").Value = Round(((Target.Value) / Range("Q43")), 0)
                Range("$F$43").Value = Round(((Target.Value) / Range("Q43") * 12), 0)
                Range("$G$43").Value = Round(((Target.Value) / Range("Q43") * 365), 0)
                Range("$L$43").Value = Round((Target.Value * 12), 0)
                Range("$M$43").Value = Round((Target.Value * 365), 0)
              Case "$K$44"
                SortieFHCompat = True
                Range("$E$44").Value = Round(((Target.Value) / Range("Q44")), 0)
                Range("$F$44").Value = Round(((Target.Value) / Range("Q44") * 12), 0)
                Range("$G$44").Value = Round(((Target.Value) / Range("Q44") * 365), 0)
                Range("$L$44").Value = Round((Target.Value * 12), 0)
                Range("$M$44").Value = Round((Target.Value * 365), 0)
              Case "$K$45"
                SortieFHCompat = True
                Range("$E$45").Value = Round(((Target.Value) / Range("Q45")), 0)
                Range("$F$45").Value = Round(((Target.Value) / Range("Q45") * 12), 0)
                Range("$G$45").Value = Round(((Target.Value) / Range("Q45") * 365), 0)
                Range("$L$45").Value = Round((Target.Value * 12), 0)
                Range("$M$45").Value = Round((Target.Value * 365), 0)

Open in new window

0
Comment
Question by:-Polak
  • 6
  • 3
10 Comments
 
LVL 14

Assisted Solution

by:Farzad Akbarnejad
Farzad Akbarnejad earned 500 total points
ID: 40372239
Hi,
I assume that your select case statement has variable Varm that you write case like the following

Select case Varm
. . . . . . . . . . . . . . . . . 
Case "$K$45"
                SortieFHCompat = True
                Range("$E$45").Value = Round(((Target.Value) / Range("Q45")), 0)
                Range("$F$45").Value = Round(((Target.Value) / Range("Q45") * 12), 0)
                Range("$G$45").Value = Round(((Target.Value) / Range("Q45") * 365), 0)
                Range("$L$45").Value = Round((Target.Value * 12), 0)
                Range("$M$45").Value = Round((Target.Value * 365), 0)

Open in new window



So you can extract column number from it and write some statement of code and don't write many cases:

nn = cint(mid(Varm, instr(2, Varm, "$")+1))

 SortieFHCompat = True
 Range("$E$" & nn).Value = Round(((Target.Value) / Range("Q" & nn)), 0)
 Range("$F$" & nn).Value = Round(((Target.Value) / Range("Q" & nn) * 12), 0)
 Range("$G$" & nn).Value = Round(((Target.Value) / Range("Q" & nn) * 365), 0)
 Range("$L$" & nn).Value = Round((Target.Value * 12), 0)
 Range("$M$" & nn).Value = Round((Target.Value * 365), 0)

Open in new window


-FA
0
 
LVL 1

Accepted Solution

by:
-Polak earned 0 total points
ID: 40373360
I'm actually using Select Case Target Address, but I figured out how to do it with the above.
One Note though if you notice every where there was a Comment in my original code the Math below it was slightly different, therefore I had to create If statements for your code. Annoyingly, you cannot simply do IF Target.Address = "E15:E33" Then  and I had to use IF Not Intersect(Target, Range("E15:E33")) Is Nothing Then
 Which is still confusing me.... but whatever it works. Here is the working code:
        If Not Intersect(Target, Range("E15:E33")) Is Nothing Then
            Select Case Target.Address
            Case Target.Address
                nn = CInt(Mid(Target.Address, InStr(2, Target.Address, "$") + 1))
                SortieFHCompat = True
                Range("$F$" & nn).Value = Round(((Target.Value * 365) / 12), 0)
                Range("$G$" & nn).Value = Round((Target.Value * 365), 0)
                Range("$K$" & nn).Value = Round((Target.Value * Range("Q" & nn)), 0)
                Range("$L$" & nn).Value = Round(((Target.Value * Range("Q" & nn)) * 365 / 12), 0)
                Range("$M$" & nn).Value = Round(((Target.Value * Range("Q" & nn)) * 365), 0)
        End Select
        End If

Open in new window

0
 
LVL 1

Author Comment

by:-Polak
ID: 40374465
I've requested that this question be closed as follows:

Accepted answer: 0 points for -Polak's comment #a40373360
Assisted answer: 500 points for Farzad Akbarnejad's comment #a40372239

for the following reason:

IF Not Statement within my own solution will aid you if you have different formulas depending on the Target.Address.
0
 
LVL 14

Expert Comment

by:Farzad Akbarnejad
ID: 40374462
Hi,

Select Case Target.Address
            Case Target.Address
End Select

statements is redundant because always the inclosed code is run.

-FA
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 14

Expert Comment

by:Farzad Akbarnejad
ID: 40374463
target address is one cell and isn't a range and you want apply your statements in that range so you must use if not statements.

-FA
0
 
LVL 14

Expert Comment

by:Farzad Akbarnejad
ID: 40374464
I think that I solved this problem ;)

-FA
0
 
LVL 14

Expert Comment

by:Farzad Akbarnejad
ID: 40374466
I think that I solved this problem ;)

-FA
0
 
LVL 1

Author Comment

by:-Polak
ID: 40374879
Never said that you didn't, further I assigned all the points to you..... Just noted that because I had different calculations based specific cases that and IF Not statement was required.
0
 
LVL 14

Expert Comment

by:Farzad Akbarnejad
ID: 40374884
Just do it as you think it right. :)

Thanks
-FA
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

762 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now