austenr
07-31-2007, 06:24 AM
Hi,
I am wondering if this code could be shortened:
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Range("B7").Select
ActiveCell.FormulaR1C1 = "0"
Range("B7").Select
Selection.Copy
Range("B8:B47").Select
ActiveSheet.Paste
SolverOk SetCell:="$C$7", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$7"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$8", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$8"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$9", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$9"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$10", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$10"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$11", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$11"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$12", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$12"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$13", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$13"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$14", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$14"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$15", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$15"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$16", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$16"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$17", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$17"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$18", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$18"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$19", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$19"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$20", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$20"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$21", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$21"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$22", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$22"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$23", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$23"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$24", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$24"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$25", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$25"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$26", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$26"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$27", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$27"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$28", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$28"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$29", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$29"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$30", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$30"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$31", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$31"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$32", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$32"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$33", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$33"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$34", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$34"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$35", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$35"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$36", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$36"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$37", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$37"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$38", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$38"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$39", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$39"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$40", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$40"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$41", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$41"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$42", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$42"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$43", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$43"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$44", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$44"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$45", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$45"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$46", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$46"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$47", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$47"
SolverSolve UserFinish:=True
ActiveWindow.SmallScroll Down:=10
Range("F5").Select
End Sub
I am wondering if this code could be shortened:
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Range("B7").Select
ActiveCell.FormulaR1C1 = "0"
Range("B7").Select
Selection.Copy
Range("B8:B47").Select
ActiveSheet.Paste
SolverOk SetCell:="$C$7", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$7"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$8", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$8"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$9", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$9"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$10", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$10"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$11", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$11"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$12", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$12"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$13", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$13"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$14", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$14"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$15", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$15"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$16", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$16"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$17", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$17"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$18", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$18"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$19", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$19"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$20", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$20"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$21", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$21"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$22", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$22"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$23", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$23"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$24", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$24"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$25", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$25"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$26", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$26"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$27", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$27"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$28", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$28"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$29", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$29"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$30", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$30"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$31", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$31"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$32", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$32"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$33", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$33"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$34", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$34"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$35", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$35"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$36", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$36"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$37", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$37"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$38", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$38"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$39", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$39"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$40", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$40"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$41", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$41"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$42", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$42"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$43", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$43"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$44", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$44"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$45", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$45"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$46", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$46"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$47", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$47"
SolverSolve UserFinish:=True
ActiveWindow.SmallScroll Down:=10
Range("F5").Select
End Sub