Использование языка программирования VBA

        If p = 2 Then

            For j = 1 To k + 1

                A(j) = Cells(j + 3, 2).Value

            Next j

        End If

        If p > 2 Then

            For j = 1 To k + 1

                A(j) = Cells(j + 3, p + 5).Value

            Next j

        End If

        For n = 1 To k + 1

            m = -1

            For j = 1 To n

                If m < A(j) + r(n + 1 - j, p) Then

                    m = A(j) + r(n + 1 - j, p)

                End If

            Next j

                Cells(n + 3, 6 + p).Value = m

            l = t

            For j = 1 To n

                If m = A(j) + r(n + 1 - j, p) Then

                    Cells(n + 6 + k, l).Value = j - 1

                    Cells(n + 6 + k, l + 1).Value = n - j

                l = l + 2

                End If

            Next j

        Next n

        t = l

    Next p

End Sub 

Модуль 4: 

Sub Раскрой()

Dim r, i1, i2, i3, i4, s, t As Integer

Dim l, a1, a2, a3, a4, a5, m As Integer

'Dim F, TT, SS, ZZ As String

l = 28

a1 = 4: a2 = 6

a3 = 9: a4 = 11

r = 4

m = Application.Min(a1, a2, a3, a4)

t = Application.Floor(l / m, 1)

For i1 = 0 To t

For i2 = 0 To t

For i3 = 0 To t

For i4 = 0 To t

s = 28 - a1 * i1 - a2 * i2 - a3 * i3 - a4 * i4

If s >= 0 And s < m Then

Cells(r, 1).Value = r - 3

Cells(r, 2).Value = i1

Cells(r, 3).Value = i2

Cells(r, 4).Value = i3

Cells(r, 5).Value = i4

Cells(r, 6).Value = s

r = r + 1

End If

Next i4

Next i3

Next i2

Next i1

Range("J4").FormulaLocal = "=СУММПРОИЗВ($I$4:$I$" & r - 1 & ";B4:B" & r - 1 & ")"

Range("K4").FormulaLocal = "=СУММПРОИЗВ($I$4:$I$" & r - 1 & ";C4:C" & r - 1 & ")"

Range("L4").FormulaLocal = "=СУММПРОИЗВ($I$4:$I$" & r - 1 & ";D4:D" & r - 1 & ")"

Range("M4").FormulaLocal = "=СУММПРОИЗВ($I$4:$I$" & r - 1 & ";E4:E" & r - 1 & ")"

Range("N4").FormulaLocal = "=СУММПРОИЗВ($I$4:$I$" & r - 1 & ";F4:F" & r - 1 & ")+B3*(СУММПРОИЗВ($I$4:$I$" & r - 1 & ";B4:B" & r - 1 & ")-J3)+C3*(СУММПРОИЗВ($I$4:$I$" & r - 1 & ";C4:C" & r - 1 & ")-K3)+D3*(СУММПРОИЗВ($I$4:$I$" & r - 1 & ";D4:D" & r - 1 & ")-L3)+E3*(СУММПРОИЗВ($I$4:$I$" & r - 1 & ";E4:E" & r - 1 & ")-M3)"

End Sub

Sub Optimum_capital_investments()

Worksheets("Опт.капитал").Activate

End Sub 

UserFORM1

Обработчик  события кнопки <OK>

Private Sub CommandButton1_Click()

If UserFor UserForm1.TextBox1.Text = "" Then GoTo ll

i = 0

Do

i = i + 1

Loop Until Worksheets("БД").Cells(i, 1) = ""

Worksheets("БД").Cells(i, 1) = UserForm1.TextBox1.Text

Worksheets("БД").Cells(i, 2) = UserForm1.TextBox3.Text

If UserForm1.CheckBox2 = True Then

   Worksheets("БД").Cells(i, 6) = "Есть"

   Else

   Worksheets("БД").Cells(i, 6) = "Нет"

End If

If UserForm1.CheckBox1 = True Then

Worksheets("БД").Cells(i, 7) = "Есть"

Else

Worksheets("БД").Cells(i, 7) = "Нет"

End If

Worksheets("БД").Cells(i, 8) = UserForm1.TextBox5.Text + " грв."

Worksheets("БД").Cells(i, 9) = UserForm1.TextBox2.Text

Worksheets("БД").Cells(i, 10) = UserForm1.TextBox6.Text + " мес." 

If UserForm1.OptionButton3 = True Then Worksheets("БД").Cells(i, 11).Value = "Есть семья"

If UserForm1.OptionButton4 = True Then Worksheets("БД").Cells(i, 11).Value = "Нет  семьи" 

If UserForm1.OptionButton5 = True Then Worksheets("БД").Cells(i, 12).Value = " M "

If UserForm1.OptionButton6 = True Then Worksheets("БД").Cells(i, 12).Value = " Ж " 

Worksheets("БД").Cells(i, 3).Value = ComboBox1.Value

Worksheets("БД").Cells(i, 4).Value = ComboBox2.Value

Worksheets("БД").Cells(i, 5).Value = ComboBox3.Value 

ll:

UserForm1.Hide

Worksheets("БД").Activate

End Sub 

Обработчик  события кнопки <Cancel>

Private Sub CommandButton2_Click()

UserForm1.Hide

Worksheets("БД").Activate

End Sub 

UserForm2

Обработчик события кнопки <OK>

Private Sub CommandButton1_Click()

Worksheets("Задание4").Range("c10:h15").Value = ""

Worksheets("Задание4").Range("j11:j16").Value = ""

  Worksheets("Задание4").Range("b2").Value = UserForm2.TextBox1

  Worksheets("Задание4").Range("a2").Value = UserForm2.TextBox2

  Worksheets("Задание4").Range("c2").Value = UserForm2.TextBox3

  UserForm2.Hide

  Range("C10:H15").FormulaArray = "=Модуль3.CALC(I11:I16)"

  Range("J11:J16").FormulaArray = "=MMULT((C10:H15),TRANSPOSE(d7:i7))"

  Range("f16").Select

  ActiveCell.FormulaR1C1 = "=large(r[-5]c[4]:rc[4],1)"

  Range("f17").Select

  ActiveCell.FormulaR1C1 = "=(match(large(r[-6]c[4]:r[-1]c[4],1),r[-6]c[4]:r[-1]c[4],0)-1)*5"

  r = Range("f16").Value

  v = Range("f17").Value

  UserForm3.Label3.Caption = Worksheets("Задание4").Range("f16")

  UserForm3.Label4.Caption = Worksheets("Задание4").Range("f17")

  UserForm3.Show

End Sub 

Обработчик события кнопки <Cancel>

Private Sub CommandButton2_Click()

UserForm2.Hide

End Sub 

UserForm3

Private Sub CommandButton1_Click()

UserForm3.Hide

End Sub