Help me Shorten a Procedure

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

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.

Farzad AkbarnejadDeveloperCommented:
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
-PolakAuthor Commented:
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

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:
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
Ultimate Tool Kit for Technology Solution Provider

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 now.

Farzad AkbarnejadDeveloperCommented:
Hi,

Select Case Target.Address
            Case Target.Address
End Select

statements is redundant because always the inclosed code is run.

-FA
0
Farzad AkbarnejadDeveloperCommented:
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
Farzad AkbarnejadDeveloperCommented:
I think that I solved this problem ;)

-FA
0
Farzad AkbarnejadDeveloperCommented:
I think that I solved this problem ;)

-FA
0
-PolakAuthor Commented:
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
Farzad AkbarnejadDeveloperCommented:
Just do it as you think it right. :)

Thanks
-FA
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
Visual Basic Classic

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.