Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • Last Modified:

Help me Shorten a Procedure

Hi Experts I have the following code running under a Worksheet_Change Sub; I was content with how I was coding it until I hit a procedure too long error. I assume that I can be shortened; however, I'm not certain of how/the syntax.  Your help is appreciated.

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

Open in new window

0
-Polak
Asked:
-Polak
  • 6
  • 3
2 Solutions
 
Farzad AkbarnejadCommented:
Hi,
I assume that your select case statement has variable Varm that you write case like the following

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

Open in new window



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

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

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

Open in new window


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

Open in new window

0
 
-PolakAuthor Commented:
I've requested that this question be closed as follows:

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

for the following reason:

IF Not Statement within my own solution will aid you if you have different formulas depending on the Target.Address.
0
Industry Leaders: 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!

 
Farzad AkbarnejadCommented:
Hi,

Select Case Target.Address
            Case Target.Address
End Select

statements is redundant because always the inclosed code is run.

-FA
0
 
Farzad AkbarnejadCommented:
target address is one cell and isn't a range and you want apply your statements in that range so you must use if not statements.

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

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

-FA
0
 
-PolakAuthor Commented:
Never said that you didn't, further I assigned all the points to you..... Just noted that because I had different calculations based specific cases that and IF Not statement was required.
0
 
Farzad AkbarnejadCommented:
Just do it as you think it right. :)

Thanks
-FA
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now