?
Solved

Help me Shorten a Procedure

Posted on 2014-10-09
10
Medium Priority
?
162 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
10 Comments
 
LVL 14

Assisted Solution

by:Farzad Akbarnejad
Farzad Akbarnejad earned 2000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month10 days, 16 hours left to enroll

770 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