Перевести логическую формулу в макрос, Excel [Версия для КПК]

Показать полную графическую версию : Перевести логическую формулу в макрос, Excel

NoBrain

19-05-2012, 19:27

Ребят помогите пожалуйста вот эту формулу представить в виде скрипта:
=ЕСЛИ(E3="";B3-B3;ЕСЛИ(B3>E3;B3-E3;0))

Пробовал так:
Range("G3").Select
ActiveCell.FormulaLocal = "=Если(E3="";B3-B3;Если(B3>E3;B3-E3;0))"
Но, судя по всему, данная процедура справедлива только для математических формул, а для логических - нет. :(

Буду премного благодарен. :)

Iska

19-05-2012, 19:55

Ребят помогите пожалуйста вот эту формулу представить в виде скрипта: »
Может быть, ввести формулу в ячейку при помощи кода VBA?
Range("G3").FormulaLocal = "=Если(E3="""";B3-B3;Если(B3>E3;B3-E3;0))"
Мне, правда, непонятен смысл вычислений вида «B3-B3».

azbest

19-05-2012, 20:08

Мне, правда, непонятен смысл вычислений вида «B3-B3».

Скорее всего ячейка B3- на разных листах, :)

NoBrain

19-05-2012, 20:46

Может быть, ввести формулу в ячейку при помощи кода VBA? »
И действительно - решение мне подходит.
Спасибо большое.

Мне, правда, непонятен смысл вычислений вида «B3-B3». »
Скорее всего ячейка B3- на разных листах, »

Я малость ступил. Мне нужно при таком раскладе "0" получить и я чего-то не сообразил написать просто "0", а решил операцию вычитая сам у себя. :lol:

NoBrain

19-05-2012, 21:10

Ребят, возможно наглею, но хотя бы кратко подскажите плз как организовать цикл, т.е.
Вот выполнилась операция (не просто 1+1, а начиная от вычисления формул, копирования и вставки в ячейки и до очистки всех не нужных ячеек), а теперь нужно сделать тоже самое еще раз и так столько раз пока значение в некоторой ячейке не будет меньше или равно, ну скажем 1000. Тогда этот цикл прекращается.

Попробовал первое что пришло в голову, но так не фурычит:
Dim sum As Integer
sum = ActiveCell.FormulaLocal = "=B3"
Do While sum >= 1000

'----------------------------
Range("G3").Select
ActiveCell.FormulaLocal = "=($I$2*C3)+B3"
Selection.AutoFill Destination:=Range("G3:G26"), Type:=xlFillDefault
Range("G3:G26").Select
Selection.Copy
Range("B3:B26").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("G3:G26").Select
Selection.ClearContents
Range("I3").Select
ActiveCell.FormulaLocal = "=B2-(I2*1000)"
Selection.Copy
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("I3").Select
Selection.ClearContents

Range("G3").Select
Range("G3").FormulaLocal = "=Если(E3="""";0;Если(B3>E3;B3-E3;0))"
Selection.AutoFill Destination:=Range("G3:G26"), Type:=xlFillDefault
Range("G3:G26").Select
Range("I3").Select
ActiveCell.FormulaLocal = "=СУММ(G3:G26)"
Range("I4").Select
ActiveCell.FormulaLocal = "=I3+B2"
Selection.Copy
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Range("L3").Select
ActiveCell.FormulaLocal = "=B3-G3"
Selection.AutoFill Destination:=Range("L3:L26"), Type:=xlFillDefault
Range("L3:L26").Select
Selection.Copy
Range("B3:B26").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("I3:I4").Select
Selection.ClearContents
Range("G3:G26").Select
Selection.ClearContents
Range("L3:L26").Select
Selection.ClearContents
Range("A1").Select
'----------------------------
Loop

По-сути мне нужно что бы выполнялись операции между
Do While sum >= 1000 и до loop
Выполнялись пока сумма в одной ячейке не достигнет значения менее 1000

Iska

19-05-2012, 22:32

…а решил операцию вычитая сам у себя. »
Вы, случаем, лет тридцать назад с ассемблером не работали? Или, может, родитель там какой, а? И, так сказать, проснулась родовая память, и… ;)

но хотя бы кратко подскажите плз как организовать цикл, т.е. »
Желательно было бы выложить образец самого файла, над которым проделываются сии манипуляции.

NoBrain

20-05-2012, 09:56

Желательно было бы выложить образец самого файла, над которым проделываются сии манипуляции. »
Ок.
Только там тип файла *.xlsm
Структуру файла оставил, названия некоторые изменил.

Смысл задачи такой.
Есть статьи расходов, для которых определена ставка с 1000 р. т.е. это минимальная единица распределения - сколько с этой суммы будет уходить в каждую статью.
Для некоторых статей есть лимит, т.е. если сумма превысила его, то излишек уходит т.с. в "общий котел".
И если сумма в этом "общем котле" опять превысит 1000, например 1230, то 1000 опять раскидывается по статьям, а 230 остаются не тронутыми, соответственно, если 8325 то раскидывается 1000*8 (т.е. операция 8 раз выполняется), 325 нетронутыми.

Один раз вся вот эта транзакция выполняется успешно, но если нужно 8 раз выполнить, то придется вручную тыкать кнопку 8 раз. А хотелось бы, что бы программа делала это сама до момента пока сумма в ячейке "общий котел" - "B2" не будет меньше 1000.

При этом самый первый модуль в цикл не входит.

Вот.

NoBrain

20-05-2012, 09:56

Вы, случаем, лет тридцать назад с ассемблером не работали? Или, может, родитель там какой, а? И, так сказать, проснулась родовая память, и… »
Не, не было. :)

NoBrain

20-05-2012, 12:04

Вопрос решен:
While Cells(2, 2) >= 1000
Code
Wend

Iska

21-05-2012, 01:24

Вопрос решен: »
Это хорошо, ибо я не очень понял, что именно Вам нужно помещать в цикл.

Но у меня для Вас всё же кое-что есть. Я сделал некоторые упрощения в приведённом Вами коде из выложенного файла:
Sub CopyValues()
'
' CopyValues Макрос
'
' Копируем значение ключевого столбца в резерв
Range("B3:B26").Select
Selection.Copy
Range("K3:K26").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

' Выбираем ячейку в которой будем производить вычисления
Range("G3").Select
' Производим вычисления
ActiveCell.FormulaLocal = "=($I$2*C3)+B3"
' Растягиваем ячейку по вертикали
Selection.AutoFill Destination:=Range("G3:G26"), Type:=xlFillDefault
Range("G3:G26").Select
' Копируем диапазон
Selection.Copy
' Выбираем диапазон, куда будем вставлять
Range("B3:B26").Select
' Вставляем значения
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
' Очищаем промежуточный стобец
Range("G3:G26").Select
Selection.ClearContents

' Для того что бы убрать значение ячейки "B2"
' производим вычисления в буферной ячеке и копируем значение из неё в нужную ячеку
Range("I3").Select
ActiveCell.FormulaLocal = "=B2-(I2*1000)"
Selection.Copy
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("I3").Select
Selection.ClearContents

' Операция учёта излишек
Range("G3").Select
Range("G3").FormulaLocal = "=Если(E3="""";0;Если(B3>E3;B3-E3;0))"
Selection.AutoFill Destination:=Range("G3:G26"), Type:=xlFillDefault
Range("G3:G26").Select
Range("I3").Select
ActiveCell.FormulaLocal = "=СУММ(G3:G26)"
Range("I4").Select
ActiveCell.FormulaLocal = "=I3+B2"
Selection.Copy
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Range("L3").Select
ActiveCell.FormulaLocal = "=B3-G3"
Selection.AutoFill Destination:=Range("L3:L26"), Type:=xlFillDefault
Range("L3:L26").Select
Selection.Copy
Range("B3:B26").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("I3:I4").Select
Selection.ClearContents
Range("G3:G26").Select
Selection.ClearContents
Range("L3:L26").Select
Selection.ClearContents
Range("A1").Select

End Sub

и получил следующее (самого алгоритма вычислений я не касался):
'
' CopyValues Макрос
'
Sub CopyValues()
Range("K3:K26").Value = Range("B3:B26").Value

Range("G3:G26").FormulaLocal = "=($I$2*C3)+B3"
Range("B3:B26").Value = Range("G3:G26").Value
Range("G3:G26").ClearContents

Range("B2").Value = Range("B2").Value - Range("I2").Value * 1000

Range("G3:G26").FormulaLocal = "=Если(E3="""";0;Если(B3>E3;B3-E3;0))"
Range("I3").FormulaLocal = "=СУММ(G3:G26)"
Range("I4").FormulaLocal = "=I3+B2"
Range("B2").Value = Range("I4").Value

Range("L3:L26").FormulaLocal = "=B3-G3"
Range("B3:B26").Value = Range("L3:L26").Value

Range("I3:I4,G3:G26,L3:L26").ClearContents
End Sub

Общие принципы:

* операции с выделением:
Range("…").Select
Selection.…
заменены на прямую работу с диапазонами:
Range("…").…
* операции по ручному копированию-вставке:
Selection.Copy
Range("…").Select
Selection.PasteSpecial …
заменены прямым присваиванием:
Range("…").Value = Range("…").Value
* операции по вставке формулы в ячейку и последующее распространение этой формулы на диапазон ячеек:
Range("…").Select
ActiveCell.FormulaLocal = "=…"
Selection.AutoFill Destination:=Range("…"), Type:=xlFillDefault

заменено на одно присваивание формулы всему потребному диапазону ячеек:
Range("…").FormulaLocal = "=…"
* очистку можно производить над объединением диапазонов:
Range("…, …, …").ClearContents

Пробуйте.

NoBrain

04-06-2012, 19:06

Iska
Спасибо за проделанную работу.
Ща протестирую.
)))

NoBrain

04-06-2012, 21:16

Проверил. Работает.
Но появилась идея еще немного оптимизировать и вот ту, пожалуй будет посложнее. ((

Буду благодарен если кто подскажет.

А мысль такая:
Операции которые фактически выполняются здесь:

Range("G3:G26").FormulaLocal = "=($I$2*C3)+B3"
....

Range("G3:G26").FormulaLocal = "=Если(E3="""";0;Если(B3>E3;B3-E3;0))"
Range("I3").FormulaLocal = "=СУММ(G3:G26)"

Cводятся к тому, что все это - промежуточные операции. Значения, которые получаются в диапазоне "G3:G26" нужны лишь как буфер для следующих операций. По итогам все очищается.
Но для как видно пришлось для выполнения сначала одной формулы:
"=($I$2*C3)+B3"
потом другой:
Если(E3="""";0;Если(B3>E3;B3-E3;0))"
использовать дополнительный диапазон ячеек в Excel.

А вот если бы помещать результата выполнения этой операции в переменную. Помещать в том же виде, как он хранится в этом диапазоне.
Что бы с ним можно было производить такие же операции:
Range("B3:B26").Value = Range("G3:G26").Value
Range("I3").FormulaLocal = "=СУММ(G3:G26)"

Вот это я честно говоря даже не представляю как сделать. :(

NoBrain

06-06-2012, 16:47

Цель - как можно больше операций переместить в код, а на листе Excel использовать как можно меньше дополнительных ячеек.

Покамест сконцентрировал свое внимание на первой позиции:

С Листа перенес в макрос формулу:
В ячейке I2 =ЕСЛИ($B$2>=100; ОКРУГЛВНИЗ($B$2/100; 0);0)

Dim zn As Integer

If Range("B2").Value >= 100 Then
zn = Int(Range("B2").Value / 100)
Else
zn = 0
End If

Получаем некоторое цело значение в переменной.
Дальше эту переменную нужно использовать в следующей операции, вместо: $I$2
Range("G3:G26").FormulaLocal = "=($I$2*C3)+B3"

Так не подходит:
Range("G3:G26").FormulaLocal = "=(zn*C3)+B3"
Range("G3:G26").Value = zn * Range("C3").Value + Range("B3").Value

А нужно что бы для диапазона ячеек выполнялась формула, которую я пытаюсь перевести в VBA код.

Как это сделать - не знаю.
Помогите пожалуйста. :(

NoBrain

06-06-2012, 17:03

Можно конечно сделать тупо вот так:
Range("G3").Value = zn * Range("C3").Value + Range("B3").Value
Range("G4").Value = zn * Range("C4").Value + Range("B4").Value
Range("G5").Value = zn * Range("C5").Value + Range("B5").Value
Range("G6").Value = zn * Range("C6").Value + Range("B6").Value
Range("G7").Value = zn * Range("C7").Value + Range("B7").Value
Range("G8").Value = zn * Range("C8").Value + Range("B8").Value
Range("G9").Value = zn * Range("C9").Value + Range("B9").Value
Range("G10").Value = zn * Range("C10").Value + Range("B10").Value
Range("G11").Value = zn * Range("C11").Value + Range("B11").Value
Range("G12").Value = zn * Range("C12").Value + Range("B12").Value
Range("G13").Value = zn * Range("C13").Value + Range("B13").Value
Range("G14").Value = zn * Range("C14").Value + Range("B14").Value
Range("G15").Value = zn * Range("C15").Value + Range("B15").Value
Range("G16").Value = zn * Range("C16").Value + Range("B16").Value
Range("G17").Value = zn * Range("C17").Value + Range("B17").Value
Range("G18").Value = zn * Range("C18").Value + Range("B18").Value
Range("G19").Value = zn * Range("C19").Value + Range("B19").Value
Range("G20").Value = zn * Range("C20").Value + Range("B20").Value
Range("G21").Value = zn * Range("C21").Value + Range("B21").Value
Range("G22").Value = zn * Range("C22").Value + Range("B22").Value
Range("G23").Value = zn * Range("C23").Value + Range("B23").Value
Range("G24").Value = zn * Range("C24").Value + Range("B24").Value
Range("G25").Value = zn * Range("C25").Value + Range("B25").Value
Range("G26").Value = zn * Range("C26").Value + Range("B26").Value
Но, на мой взгляд - это глупо.

NoBrain

06-06-2012, 18:43

Никто не может подсказать? :(

NoBrain

06-06-2012, 20:33

Фактически можно сделать вывод, что цель это эмулировать некую группу ячеек (столбец или строку) в языке программирования Basic.
Если ли способы решения этого вопроса?