Всем доброго времени суток. Нужно найти журнал с наибольшей прибылью и вывести его значение. Т.е. Нужно 8 строку умножить на строку 17 и выбрать максимум, и вывести наименование этого журнала,но не могу понять,как написать это на VBA. Благодарю за ответы. Скриншот: Листинг программы: [vba]
Код
Dim Journal(15) As Variant
Dim PriceJanuary(15) As Long Dim PriceFebruary(15) As Long Dim PriceMarch(15) As Long Dim PriceApril(15) As Long Dim PriceMay(15) As Long Dim PriceJune(15) As Long
Dim January(15) As Long Dim February(15) As Long Dim March(15) As Long Dim April(15) As Long Dim May(15) As Long Dim June(15) As Long
For i = 1 To 15 1 PriceJanuary(i) = InputBox("Введите цену для журнала " & Journal(i) & " за Январь") Cells(3, i + 1) = PriceJanuary(i) Next i
For i = 1 To 15 PriceFebruary(i) = InputBox("Введите цену для журнала " & Journal(i) & " за Февраль") Cells(4, i + 1) = PriceFebruary(i) Next i
For i = 1 To 15 PriceMarch(i) = InputBox("Введите цену для журнала " & Journal(i) & " за Март") Cells(5, i + 1) = PriceMarch(i) Next i
For i = 1 To 15 PriceApril(i) = InputBox("Введите цену для журнала " & Journal(i) & " за Апрель") Cells(6, i + 1) = PriceApril(i) Next i
For i = 1 To 15 PriceMay(i) = InputBox("Введите цену для журнала " & Journal(i) & " за Май") Cells(7, i + 1) = PriceMay(i) Next i
For i = 1 To 15 PriceJune(i) = InputBox("Введите цену для журнала " & Journal(i) & " за Июнь") Cells(8, i + 1) = PriceJune(i) Next i
End Sub
Sub Sell()
For i = 1 To 15 January(i) = InputBox("Введите кол-во проданных журналов " & Journal(i) & " за Январь") Cells(12, i + 1) = January(i) Next i
For i = 1 To 15 February(i) = InputBox("Введите кол-во проданных журналов " & Journal(i) & " за Февраль") Cells(13, i + 1) = February(i) Next i
For i = 1 To 15 March(i) = InputBox("Введите кол-во проданных журналов " & Journal(i) & " за Март") Cells(14, i + 1) = March(i) Next i
For i = 1 To 15 April(i) = InputBox("Введите кол-во проданных журналов " & Journal(i) & " за Апрель") Cells(15, i + 1) = April(i) Next i
For i = 1 To 15 May(i) = InputBox("Введите кол-во проданных журналов " & Journal(i) & " за Май") Cells(16, i + 1) = May(i) Next i
For i = 1 To 15 June(i) = InputBox("Введите кол-во проданных журналов " & Journal(i) & " за Июнь") Cells(17, i + 1) = June(i) Next i
End Sub
Sub TwoSell() For i = 1 To 15 Cells(18, 1 + i) = (January(i) * PriceJanuary(i)) + (February(i) * PriceFebruary(i)) Next i
End Sub
Sub AllSell()
Dim One As Integer Dim Two As Integer Dim Three As Integer Dim Four As Integer Dim Five As Integer Dim Six As Integer
For i = 1 To 15 One = One + January(i) * PriceJanuary(i) Cells(12, 17) = One Next i
For i = 1 To 15 Two = Two + February(i) * PriceFebruary(i) Cells(13, 17) = Two Next i
For i = 1 To 15 Three = Three + March(i) * PriceMarch(i) Cells(14, 17) = Three Next i
For i = 1 To 15 Four = Four + April(i) * PriceApril(i) Cells(15, 17) = Four Next i
For i = 1 To 15 Five = Five + May(i) * PriceMay(i) Cells(16, 17) = Five Next i
For i = 1 To 15 Six = Six + June(i) * PriceJune(i) Cells(17, 17) = Six Next i End Sub
Sub FullAll() Dim Full As Integer
For i = 1 To 15 Full = Full + (January(i) * PriceJanuary(i)) + (February(i) * PriceFebruary(i)) + (March(i) * PriceMarch(i)) + (April(i) * PriceApril(i)) + (May(i) * PriceMay(i)) + (June(i) * PriceJune(i)) Cells(19, 2) = Full Next i End Sub
[/vba]
Всем доброго времени суток. Нужно найти журнал с наибольшей прибылью и вывести его значение. Т.е. Нужно 8 строку умножить на строку 17 и выбрать максимум, и вывести наименование этого журнала,но не могу понять,как написать это на VBA. Благодарю за ответы. Скриншот: Листинг программы: [vba]
Код
Dim Journal(15) As Variant
Dim PriceJanuary(15) As Long Dim PriceFebruary(15) As Long Dim PriceMarch(15) As Long Dim PriceApril(15) As Long Dim PriceMay(15) As Long Dim PriceJune(15) As Long
Dim January(15) As Long Dim February(15) As Long Dim March(15) As Long Dim April(15) As Long Dim May(15) As Long Dim June(15) As Long
For i = 1 To 15 1 PriceJanuary(i) = InputBox("Введите цену для журнала " & Journal(i) & " за Январь") Cells(3, i + 1) = PriceJanuary(i) Next i
For i = 1 To 15 PriceFebruary(i) = InputBox("Введите цену для журнала " & Journal(i) & " за Февраль") Cells(4, i + 1) = PriceFebruary(i) Next i
For i = 1 To 15 PriceMarch(i) = InputBox("Введите цену для журнала " & Journal(i) & " за Март") Cells(5, i + 1) = PriceMarch(i) Next i
For i = 1 To 15 PriceApril(i) = InputBox("Введите цену для журнала " & Journal(i) & " за Апрель") Cells(6, i + 1) = PriceApril(i) Next i
For i = 1 To 15 PriceMay(i) = InputBox("Введите цену для журнала " & Journal(i) & " за Май") Cells(7, i + 1) = PriceMay(i) Next i
For i = 1 To 15 PriceJune(i) = InputBox("Введите цену для журнала " & Journal(i) & " за Июнь") Cells(8, i + 1) = PriceJune(i) Next i
End Sub
Sub Sell()
For i = 1 To 15 January(i) = InputBox("Введите кол-во проданных журналов " & Journal(i) & " за Январь") Cells(12, i + 1) = January(i) Next i
For i = 1 To 15 February(i) = InputBox("Введите кол-во проданных журналов " & Journal(i) & " за Февраль") Cells(13, i + 1) = February(i) Next i
For i = 1 To 15 March(i) = InputBox("Введите кол-во проданных журналов " & Journal(i) & " за Март") Cells(14, i + 1) = March(i) Next i
For i = 1 To 15 April(i) = InputBox("Введите кол-во проданных журналов " & Journal(i) & " за Апрель") Cells(15, i + 1) = April(i) Next i
For i = 1 To 15 May(i) = InputBox("Введите кол-во проданных журналов " & Journal(i) & " за Май") Cells(16, i + 1) = May(i) Next i
For i = 1 To 15 June(i) = InputBox("Введите кол-во проданных журналов " & Journal(i) & " за Июнь") Cells(17, i + 1) = June(i) Next i
End Sub
Sub TwoSell() For i = 1 To 15 Cells(18, 1 + i) = (January(i) * PriceJanuary(i)) + (February(i) * PriceFebruary(i)) Next i
End Sub
Sub AllSell()
Dim One As Integer Dim Two As Integer Dim Three As Integer Dim Four As Integer Dim Five As Integer Dim Six As Integer
For i = 1 To 15 One = One + January(i) * PriceJanuary(i) Cells(12, 17) = One Next i
For i = 1 To 15 Two = Two + February(i) * PriceFebruary(i) Cells(13, 17) = Two Next i
For i = 1 To 15 Three = Three + March(i) * PriceMarch(i) Cells(14, 17) = Three Next i
For i = 1 To 15 Four = Four + April(i) * PriceApril(i) Cells(15, 17) = Four Next i
For i = 1 To 15 Five = Five + May(i) * PriceMay(i) Cells(16, 17) = Five Next i
For i = 1 To 15 Six = Six + June(i) * PriceJune(i) Cells(17, 17) = Six Next i End Sub
Sub FullAll() Dim Full As Integer
For i = 1 To 15 Full = Full + (January(i) * PriceJanuary(i)) + (February(i) * PriceFebruary(i)) + (March(i) * PriceMarch(i)) + (April(i) * PriceApril(i)) + (May(i) * PriceMay(i)) + (June(i) * PriceJune(i)) Cells(19, 2) = Full Next i End Sub
Sub tt() c1_ = Cells(2, Columns.Count).End(1).Column r1_ = Cells(Rows.Count, c1_).End(3).Row - 1 r0_ = 3 mes_ = Range("A" & r1_) For i = r0_ To r1_ If Range("A" & i) = mes_ Then Exit For End If Next i For j = 2 To c1_ z1_ = Cells(i, j) * Cells(r1_, j) If z1_ > z2_ Then z2_ = z1_ nc_ = j End If Next j Range("B" & r1_ + 3) = Cells(2, nc_) End Sub
[/vba]
Такой вариант [vba]
Код
Sub tt() c1_ = Cells(2, Columns.Count).End(1).Column r1_ = Cells(Rows.Count, c1_).End(3).Row - 1 r0_ = 3 mes_ = Range("A" & r1_) For i = r0_ To r1_ If Range("A" & i) = mes_ Then Exit For End If Next i For j = 2 To c1_ z1_ = Cells(i, j) * Cells(r1_, j) If z1_ > z2_ Then z2_ = z1_ nc_ = j End If Next j Range("B" & r1_ + 3) = Cells(2, nc_) End Sub