Solved

Help me Shorten a Procedure

Posted on 2014-10-09
10
151 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
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.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

862 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

24 Experts available now in Live!

Get 1:1 Help Now