Приветик всем, давно тут не появлялся, уже все забыл, в том числе и VBA. Пытаюсь написать простейший макрос, но где-то видать у меня синтаксис хромает. Прошу помочь, задачка вроде на один зубок. В файле, для того, чтобы вычислить минимальное значение (зеленым пометил), при котором второй столбец сойдет на 0 за 50 строк, я подбираю вручную. Так вот хотелось бы сделать на это кнопку с макросом. Макрос пишу для ячейки с красным числом. Вот что я наваял [vba]
Код
Sub Min() Dim a1, a2, b1, b2, c1, c2, e1, e2, f1, f2 Dim d As Integer Dim i As Long a1 = Range("B6") a2 = Range("D6") b1 = Range("B7") b2 = Range("D7") c1 = 0 c2 = Range("D10") Do While c2 > 0 c1 = c1 + 1 f1 = c1 f2 = c2 For i = 1 To 50 If i < 10 Then d = 1 Else If i < 20 Then d = 2 Else If i < 30 Then d = 4 Else If i < 40 Then d = 8 Else If i < 50 Then d = 16 Else d = 32 If ((f2 * a2) - (f1 * b1) * d) < 0 Then e2 = 0 Else e2 = Int((((f2 * a2) - (f1 * b1) * d) / a1) + 0.99) If ((f1 * a1) - (f2 * b2) * d) < 0 Then e1 = 0 Else e1 = Int((((f1 * a1) - (f2 * b2) * d) / a2) + 0.99) f1 = e1 f2 = e2 Next i c2 = e2 Loop Range("B10").Value = e1 End Sub
[/vba]
Но мне выдает ошибку 6 Overflow
Приветик всем, давно тут не появлялся, уже все забыл, в том числе и VBA. Пытаюсь написать простейший макрос, но где-то видать у меня синтаксис хромает. Прошу помочь, задачка вроде на один зубок. В файле, для того, чтобы вычислить минимальное значение (зеленым пометил), при котором второй столбец сойдет на 0 за 50 строк, я подбираю вручную. Так вот хотелось бы сделать на это кнопку с макросом. Макрос пишу для ячейки с красным числом. Вот что я наваял [vba]
Код
Sub Min() Dim a1, a2, b1, b2, c1, c2, e1, e2, f1, f2 Dim d As Integer Dim i As Long a1 = Range("B6") a2 = Range("D6") b1 = Range("B7") b2 = Range("D7") c1 = 0 c2 = Range("D10") Do While c2 > 0 c1 = c1 + 1 f1 = c1 f2 = c2 For i = 1 To 50 If i < 10 Then d = 1 Else If i < 20 Then d = 2 Else If i < 30 Then d = 4 Else If i < 40 Then d = 8 Else If i < 50 Then d = 16 Else d = 32 If ((f2 * a2) - (f1 * b1) * d) < 0 Then e2 = 0 Else e2 = Int((((f2 * a2) - (f1 * b1) * d) / a1) + 0.99) If ((f1 * a1) - (f2 * b2) * d) < 0 Then e1 = 0 Else e1 = Int((((f1 * a1) - (f2 * b2) * d) / a2) + 0.99) f1 = e1 f2 = e2 Next i c2 = e2 Loop Range("B10").Value = e1 End Sub
Ну да, все правильно, переполнение. У Вас е2 (а следовательно, и с2, и f2) увеличивается очень быстро и на некотором этапе f2 становится равно 1,21379034224578E+306. Следующей итерацией следует число, вызывающее переполнение. Вы опишите словами логику расчета, по коду не очень понятно.
Ну да, все правильно, переполнение. У Вас е2 (а следовательно, и с2, и f2) увеличивается очень быстро и на некотором этапе f2 становится равно 1,21379034224578E+306. Следующей итерацией следует число, вызывающее переполнение. Вы опишите словами логику расчета, по коду не очень понятно._Boroda_
Хм, веселенько. Как раз таки с2 по моей логике должно уменьшаться от значения ячейки D10 и до 0. А вот как оно станет 0 - вот то значение с1 мне и надо в ячейке B10 в конце макроса.
В цикле For я просчитываю все 50 строк, в этом расчете в каждой строке (в каждом шаге i) c2 и c1 должны уменьшаться. Если один из них станет 0, то до конца цикла For значения с1 и с2 уже не должны меняться. Циклом Do я постепенно увеличиваю с1 на единицу, до тех пор пока с2 не станет после цикла For равняться 0.
Хм, веселенько. Как раз таки с2 по моей логике должно уменьшаться от значения ячейки D10 и до 0. А вот как оно станет 0 - вот то значение с1 мне и надо в ячейке B10 в конце макроса.
В цикле For я просчитываю все 50 строк, в этом расчете в каждой строке (в каждом шаге i) c2 и c1 должны уменьшаться. Если один из них станет 0, то до конца цикла For значения с1 и с2 уже не должны меняться. Циклом Do я постепенно увеличиваю с1 на единицу, до тех пор пока с2 не станет после цикла For равняться 0.amur84
Таааак, нашел опечаточку, вроде, в двух последних опереторах If a2 и a1 в конце перепутаны, вроде ошибки уже не выскакивает, но значение возвращает 0. А надо 5022.
Таааак, нашел опечаточку, вроде, в двух последних опереторах If a2 и a1 в конце перепутаны, вроде ошибки уже не выскакивает, но значение возвращает 0. А надо 5022.amur84
Про это-то как раз я догадался. Я спрашивал про общую логику - что-почему-откуда-куда. И в файле есть столбцы с формулами - что они должны считать и по какому алгоритму? [p.s.]Кстати, довольно часто бывает достаточно просто словами описать это "что-почему-откуда-куда" - и сразу самостоятельно ошибку в логике макроса или формулы найдете.
Про это-то как раз я догадался. Я спрашивал про общую логику - что-почему-откуда-куда. И в файле есть столбцы с формулами - что они должны считать и по какому алгоритму? [p.s.]Кстати, довольно часто бывает достаточно просто словами описать это "что-почему-откуда-куда" - и сразу самостоятельно ошибку в логике макроса или формулы найдете.
_Boroda_, Ну хорошо, давайте совсем подробно. В файле для макроса важен только диапазон B6:D10, оттуда он берет 5 значений исходных данных и по ним должен найти 6-е значение и вставить в ячейку B10. Все остальное в файле - это по сути демонстрация, как должен считать макрос. Я ручками меняю значение ячейки F10 (для макроса это B10) до тех пор пока в столбце H не появятся нули. Можете сами попробовать изменить значение с 5022 на 5021. То есть в столбцах F и H пляшут значения c1 и c2, столбцы L и N у меня вспомогательные, там происходит вычисление типа [vba]
[/vba] а уже в F и H деление и округление до целых вверх.
_Boroda_, Ну хорошо, давайте совсем подробно. В файле для макроса важен только диапазон B6:D10, оттуда он берет 5 значений исходных данных и по ним должен найти 6-е значение и вставить в ячейку B10. Все остальное в файле - это по сути демонстрация, как должен считать макрос. Я ручками меняю значение ячейки F10 (для макроса это B10) до тех пор пока в столбце H не появятся нули. Можете сами попробовать изменить значение с 5022 на 5021. То есть в столбцах F и H пляшут значения c1 и c2, столбцы L и N у меня вспомогательные, там происходит вычисление типа [vba]
Вроде разобрался в чем косяк: у меня в расчетах в столбцах L и N первые формулы неного отличаются от всех последующих, а в макросе я их под одну гребенку засунул. Завтра попробую допилить...
Вроде разобрался в чем косяк: у меня в расчетах в столбцах L и N первые формулы неного отличаются от всех последующих, а в макросе я их под одну гребенку засунул. Завтра попробую допилить...amur84
_Boroda_, все получилось, спасибо за подсказку с переполнением, а то я бы долго искал эту и следующие опечатки. Код получился такой [vba]
Код
Sub Min() Dim a1, a2, c1, c2, e1, e2, f1, f2 As Long Dim i As Long c1 = 1 c2 = Range("D10") Range("B10") = "считаю" Do f1 = c1 f2 = c2 a1 = f1 * Range("B7") a2 = f2 * Range("D7") For i = 1 To 50 If (a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) < 0 Then e2 = 0 Else e2 = Int(((a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) / Range("D7")) + 0.99) If (a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) < 0 Then e1 = 0 Else e1 = Int(((a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) / Range("B7")) + 0.99) a2 = a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10)) a1 = a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10)) f1 = e1 f2 = e2 Next i c1 = c1 + 1 Loop While e2 > 0 Range("B10").Value = c1 - 1 End Sub
[/vba] Но вот одно напрягает - продолжительность расчета, для поиска числа 5022 ему требуется около 30 секунд. Есть ли какие-нибудь способы ускорить эту канитель? Вот мне на ум пока что приходит только многоступенчатый оператор If...Then...Else, где будет выполняться цикл For для определенных значений с1, а в зависимости от получающейся е2 уже выполняться оператор Do в определенном диапазоне (ну или уже заменить его на For). Код, конечно же, станет в разы больше, но количество проходок всех циклов For можно будет снизить например до (примерно) 550.
_Boroda_, все получилось, спасибо за подсказку с переполнением, а то я бы долго искал эту и следующие опечатки. Код получился такой [vba]
Код
Sub Min() Dim a1, a2, c1, c2, e1, e2, f1, f2 As Long Dim i As Long c1 = 1 c2 = Range("D10") Range("B10") = "считаю" Do f1 = c1 f2 = c2 a1 = f1 * Range("B7") a2 = f2 * Range("D7") For i = 1 To 50 If (a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) < 0 Then e2 = 0 Else e2 = Int(((a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) / Range("D7")) + 0.99) If (a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) < 0 Then e1 = 0 Else e1 = Int(((a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) / Range("B7")) + 0.99) a2 = a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10)) a1 = a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10)) f1 = e1 f2 = e2 Next i c1 = c1 + 1 Loop While e2 > 0 Range("B10").Value = c1 - 1 End Sub
[/vba] Но вот одно напрягает - продолжительность расчета, для поиска числа 5022 ему требуется около 30 секунд. Есть ли какие-нибудь способы ускорить эту канитель? Вот мне на ум пока что приходит только многоступенчатый оператор If...Then...Else, где будет выполняться цикл For для определенных значений с1, а в зависимости от получающейся е2 уже выполняться оператор Do в определенном диапазоне (ну или уже заменить его на For). Код, конечно же, станет в разы больше, но количество проходок всех циклов For можно будет снизить например до (примерно) 550.amur84
_Boroda_, спасибо, почитал статейку, этот оператор не использовал, но статья наткнула на другую мысль - достаточно быстро переделал код. Теперь считает в один момент! [vba]
Код
Sub Min() Dim a1, a2, c1, c2, e1, e2, f1, f2 As Long Dim i As Long c1 = 0 c2 = Range("D10") Do c1 = c1 + 100000 f1 = c1 f2 = c2 a1 = f1 * Range("B7") a2 = f2 * Range("D7") For i = 1 To 50 If (a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) < 0 Then e2 = 0 Else e2 = Int(((a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) / Range("D7")) + 0.99) If (a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) < 0 Then e1 = 0 Else e1 = Int(((a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) / Range("B7")) + 0.99) a2 = a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10)) a1 = a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10)) f1 = e1 f2 = e2 Next i Loop While e2 > 0 c1 = c1 - 100000 Do c1 = c1 + 10000 f1 = c1 f2 = c2 a1 = f1 * Range("B7") a2 = f2 * Range("D7") For i = 1 To 50 If (a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) < 0 Then e2 = 0 Else e2 = Int(((a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) / Range("D7")) + 0.99) If (a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) < 0 Then e1 = 0 Else e1 = Int(((a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) / Range("B7")) + 0.99) a2 = a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10)) a1 = a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10)) f1 = e1 f2 = e2 Next i Loop While e2 > 0 c1 = c1 - 10000 Do c1 = c1 + 1000 f1 = c1 f2 = c2 a1 = f1 * Range("B7") a2 = f2 * Range("D7") For i = 1 To 50 If (a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) < 0 Then e2 = 0 Else e2 = Int(((a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) / Range("D7")) + 0.99) If (a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) < 0 Then e1 = 0 Else e1 = Int(((a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) / Range("B7")) + 0.99) a2 = a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10)) a1 = a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10)) f1 = e1 f2 = e2 Next i Loop While e2 > 0 c1 = c1 - 1000 Do c1 = c1 + 100 f1 = c1 f2 = c2 a1 = f1 * Range("B7") a2 = f2 * Range("D7") For i = 1 To 50 If (a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) < 0 Then e2 = 0 Else e2 = Int(((a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) / Range("D7")) + 0.99) If (a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) < 0 Then e1 = 0 Else e1 = Int(((a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) / Range("B7")) + 0.99) a2 = a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10)) a1 = a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10)) f1 = e1 f2 = e2 Next i Loop While e2 > 0 c1 = c1 - 100 Do c1 = c1 + 10 f1 = c1 f2 = c2 a1 = f1 * Range("B7") a2 = f2 * Range("D7") For i = 1 To 50 If (a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) < 0 Then e2 = 0 Else e2 = Int(((a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) / Range("D7")) + 0.99) If (a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) < 0 Then e1 = 0 Else e1 = Int(((a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) / Range("B7")) + 0.99) a2 = a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10)) a1 = a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10)) f1 = e1 f2 = e2 Next i Loop While e2 > 0 c1 = c1 - 10 Do c1 = c1 + 1 f1 = c1 f2 = c2 a1 = f1 * Range("B7") a2 = f2 * Range("D7") For i = 1 To 50 If (a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) < 0 Then e2 = 0 Else e2 = Int(((a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) / Range("D7")) + 0.99) If (a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) < 0 Then e1 = 0 Else e1 = Int(((a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) / Range("B7")) + 0.99) a2 = a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10)) a1 = a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10)) f1 = e1 f2 = e2 Next i Loop While e2 > 0 Range("B10").Value = c1 End Sub
[/vba] Еще раз спаибо за советы!
_Boroda_, спасибо, почитал статейку, этот оператор не использовал, но статья наткнула на другую мысль - достаточно быстро переделал код. Теперь считает в один момент! [vba]
Код
Sub Min() Dim a1, a2, c1, c2, e1, e2, f1, f2 As Long Dim i As Long c1 = 0 c2 = Range("D10") Do c1 = c1 + 100000 f1 = c1 f2 = c2 a1 = f1 * Range("B7") a2 = f2 * Range("D7") For i = 1 To 50 If (a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) < 0 Then e2 = 0 Else e2 = Int(((a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) / Range("D7")) + 0.99) If (a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) < 0 Then e1 = 0 Else e1 = Int(((a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) / Range("B7")) + 0.99) a2 = a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10)) a1 = a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10)) f1 = e1 f2 = e2 Next i Loop While e2 > 0 c1 = c1 - 100000 Do c1 = c1 + 10000 f1 = c1 f2 = c2 a1 = f1 * Range("B7") a2 = f2 * Range("D7") For i = 1 To 50 If (a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) < 0 Then e2 = 0 Else e2 = Int(((a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) / Range("D7")) + 0.99) If (a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) < 0 Then e1 = 0 Else e1 = Int(((a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) / Range("B7")) + 0.99) a2 = a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10)) a1 = a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10)) f1 = e1 f2 = e2 Next i Loop While e2 > 0 c1 = c1 - 10000 Do c1 = c1 + 1000 f1 = c1 f2 = c2 a1 = f1 * Range("B7") a2 = f2 * Range("D7") For i = 1 To 50 If (a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) < 0 Then e2 = 0 Else e2 = Int(((a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) / Range("D7")) + 0.99) If (a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) < 0 Then e1 = 0 Else e1 = Int(((a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) / Range("B7")) + 0.99) a2 = a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10)) a1 = a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10)) f1 = e1 f2 = e2 Next i Loop While e2 > 0 c1 = c1 - 1000 Do c1 = c1 + 100 f1 = c1 f2 = c2 a1 = f1 * Range("B7") a2 = f2 * Range("D7") For i = 1 To 50 If (a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) < 0 Then e2 = 0 Else e2 = Int(((a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) / Range("D7")) + 0.99) If (a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) < 0 Then e1 = 0 Else e1 = Int(((a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) / Range("B7")) + 0.99) a2 = a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10)) a1 = a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10)) f1 = e1 f2 = e2 Next i Loop While e2 > 0 c1 = c1 - 100 Do c1 = c1 + 10 f1 = c1 f2 = c2 a1 = f1 * Range("B7") a2 = f2 * Range("D7") For i = 1 To 50 If (a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) < 0 Then e2 = 0 Else e2 = Int(((a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) / Range("D7")) + 0.99) If (a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) < 0 Then e1 = 0 Else e1 = Int(((a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) / Range("B7")) + 0.99) a2 = a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10)) a1 = a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10)) f1 = e1 f2 = e2 Next i Loop While e2 > 0 c1 = c1 - 10 Do c1 = c1 + 1 f1 = c1 f2 = c2 a1 = f1 * Range("B7") a2 = f2 * Range("D7") For i = 1 To 50 If (a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) < 0 Then e2 = 0 Else e2 = Int(((a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10))) / Range("D7")) + 0.99) If (a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) < 0 Then e1 = 0 Else e1 = Int(((a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10))) / Range("B7")) + 0.99) a2 = a2 - (f1 * Range("B6")) * 2 ^ (Int(i / 10)) a1 = a1 - (f2 * Range("D6")) * 2 ^ (Int(i / 10)) f1 = e1 f2 = e2 Next i Loop While e2 > 0 Range("B10").Value = c1 End Sub
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
