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