Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/В место формулы макрос - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » В место формулы макрос (Макросы/Sub)
В место формулы макрос
lebensvoll Дата: Вторник, 30.10.2018, 16:08 | Сообщение № 1
Группа: Проверенные
Ранг: Ветеран
Сообщений: 828
Репутация: 15 ±
Замечаний: 0% ±

Excel 2010
Добрый день, многоуважаемые форумчане!!!
Прошу Вас помочь в решении.
Имеется файл (и думаю что в будущем будет весить очень много). Хотелось бы избежать этого и формулы перевести в код vba. На листе "1С Списание" в столбце "N" производится расчет по себестоимости. Хочется скрыть формулы (но не запоролить лист нет) но чтоб эти формулы работали на протяжении пополнения информации в умной таблице
Попытался произвести запись, но почему то цвет текста красный (((( (скорее всего не верный перенос текста).

А также возможно ли предусматреть в коде вот что
Вдруг таблица на листе "Цены на инертные" будет дополняться информацией о добавке другого вида. Соответственно в таблице на листе "1с Списание" также будет дополняться столбец с этой добавкой и чтоб в будущем уже не править код а автоматизировать (учесть) дополнение в расчете
Заранее спасибо Вам за помощь
К сообщению приложен файл: 5028172.xlsm(66.7 Kb)


Кто бы ты ни был, мир в твоих руках

Сообщение отредактировал lebensvoll - Вторник, 30.10.2018, 16:12
 
Ответить
СообщениеДобрый день, многоуважаемые форумчане!!!
Прошу Вас помочь в решении.
Имеется файл (и думаю что в будущем будет весить очень много). Хотелось бы избежать этого и формулы перевести в код vba. На листе "1С Списание" в столбце "N" производится расчет по себестоимости. Хочется скрыть формулы (но не запоролить лист нет) но чтоб эти формулы работали на протяжении пополнения информации в умной таблице
Попытался произвести запись, но почему то цвет текста красный (((( (скорее всего не верный перенос текста).

А также возможно ли предусматреть в коде вот что
Вдруг таблица на листе "Цены на инертные" будет дополняться информацией о добавке другого вида. Соответственно в таблице на листе "1с Списание" также будет дополняться столбец с этой добавкой и чтоб в будущем уже не править код а автоматизировать (учесть) дополнение в расчете
Заранее спасибо Вам за помощь

Автор - lebensvoll
Дата добавления - 30.10.2018 в 16:08
_Boroda_ Дата: Вторник, 30.10.2018, 17:39 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13502
Репутация: 5526 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так нужно?
Формулу переписал. Всё, что до столбца СП-3, делится на 1000, после (включительно) - не делится
[vba]
Код
        Range("Нормы_расхода[Себестоимость]").FormulaR1C1 = _
        "=SUMPRODUCT(INDEX(Цены_инертные[[Цемент]:[Криопласт]],SUMPRODUCT(ROW(Цены_инертные[Начало])" & _
        "*(Цены_инертные[Начало]>0)*(Цены_инертные[Начало]<=[@Начало])*((Цены_инертные[Окончание]="""")" & _
        "*99999+Цены_инертные[Окончание]>=[@Окончание]))-1,)*Нормы_расхода[@[Цемент]:[Криопласт]]" & _
        "/1000^(COLUMN(Нормы_расхода[@[Цемент]:[Криопласт]])<COLUMN([@[СП-3]])))"
[/vba]
К сообщению приложен файл: 5028172_1.xlsm(64.1 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак нужно?
Формулу переписал. Всё, что до столбца СП-3, делится на 1000, после (включительно) - не делится
[vba]
Код
        Range("Нормы_расхода[Себестоимость]").FormulaR1C1 = _
        "=SUMPRODUCT(INDEX(Цены_инертные[[Цемент]:[Криопласт]],SUMPRODUCT(ROW(Цены_инертные[Начало])" & _
        "*(Цены_инертные[Начало]>0)*(Цены_инертные[Начало]<=[@Начало])*((Цены_инертные[Окончание]="""")" & _
        "*99999+Цены_инертные[Окончание]>=[@Окончание]))-1,)*Нормы_расхода[@[Цемент]:[Криопласт]]" & _
        "/1000^(COLUMN(Нормы_расхода[@[Цемент]:[Криопласт]])<COLUMN([@[СП-3]])))"
[/vba]

Автор - _Boroda_
Дата добавления - 30.10.2018 в 17:39
lebensvoll Дата: Среда, 31.10.2018, 10:20 | Сообщение № 3
Группа: Проверенные
Ранг: Ветеран
Сообщений: 828
Репутация: 15 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, добрейшего утра!!!
:o да как так то... Вы даже укоротили формулу в два раза, ГЕНИАЛЬНО hands
НО!!!
Цитата
Хочется скрыть формулы (но не запоролить лист нет) но чтоб эти формулы работали на протяжении пополнения информации в умной таблице

решил дополнить Ваш код вот так вот:
[vba]
Код
'очищаем столбец от формул и оставляем лишь значение
    Range("Списание[Себестоимость]").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
[/vba]
Все получилось формул нет а значения остались :D
НО!!!
При этом, если начинаешь пополнять таблицу другими данными :'( в столбце "себестоимость" не производится расчет :'(
Как сделать так чтоб данный макрос, работал на этом листе всегда (я так понимаю если в ячейке будет формула то это ведь лишняя информация в объеме а также зачем людям знать что и как рассчитывается). Формула работает всегда при пополнении новой информации в данной таблице и сразу же после расчета отображала лишь значение.
Цитата
у меня есть огроменный код где подобное уже делалось, но я не могу разобраться где именно и как это там прописано :'(

Заранее спасибо!!!
К сообщению приложен файл: 6191236.xlsm(66.2 Kb)


Кто бы ты ни был, мир в твоих руках
 
Ответить
Сообщение_Boroda_, добрейшего утра!!!
:o да как так то... Вы даже укоротили формулу в два раза, ГЕНИАЛЬНО hands
НО!!!
Цитата
Хочется скрыть формулы (но не запоролить лист нет) но чтоб эти формулы работали на протяжении пополнения информации в умной таблице

решил дополнить Ваш код вот так вот:
[vba]
Код
'очищаем столбец от формул и оставляем лишь значение
    Range("Списание[Себестоимость]").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
[/vba]
Все получилось формул нет а значения остались :D
НО!!!
При этом, если начинаешь пополнять таблицу другими данными :'( в столбце "себестоимость" не производится расчет :'(
Как сделать так чтоб данный макрос, работал на этом листе всегда (я так понимаю если в ячейке будет формула то это ведь лишняя информация в объеме а также зачем людям знать что и как рассчитывается). Формула работает всегда при пополнении новой информации в данной таблице и сразу же после расчета отображала лишь значение.
Цитата
у меня есть огроменный код где подобное уже делалось, но я не могу разобраться где именно и как это там прописано :'(

Заранее спасибо!!!

Автор - lebensvoll
Дата добавления - 31.10.2018 в 10:20
StoTisteg Дата: Среда, 31.10.2018, 10:27 | Сообщение № 4
Группа: Авторы
Ранг: Старожил
Сообщений: 1049
Репутация: 89 ±
Замечаний: 0% ±

Excel 2010
Как сделать так чтоб данный макрос, работал на этом листе всегда
Повесить его вызов на событие Worksheet_Change.


Интуитивно понятный код - это когда интуитивно понятно, что это код.
 
Ответить
Сообщение
Как сделать так чтоб данный макрос, работал на этом листе всегда
Повесить его вызов на событие Worksheet_Change.

Автор - StoTisteg
Дата добавления - 31.10.2018 в 10:27
lebensvoll Дата: Среда, 31.10.2018, 10:44 | Сообщение № 5
Группа: Проверенные
Ранг: Ветеран
Сообщений: 828
Репутация: 15 ±
Замечаний: 0% ±

Excel 2010
StoTisteg, спасибо огромное за ответ!
я теряюсь в дальнейшем (((( прекрасно осознаю что
1.данный код нужно прописать в самом листе
2. Worksheet_Change (Событие изменения содержимого ячейки)
3. дальше ведь я должен объявить эти переменные %) через Dim (вроде бы)
затем лишь код формулы
и после уже очищение столбца от формул и оставляем значение
Получается так (((( но увы
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("Списание[Себестоимость]") Then
  'расчет в таблице макросом (на листе: "1С списание")
    Range("Списание[Себестоимость]").FormulaR1C1 = _
        "=IFERROR(SUMPRODUCT(INDEX(Цены_инертные[[Цемент]:[Криопласт]],SUMPRODUCT(ROW(Цены_инертные[Начало])" & _
        "*(Цены_инертные[Начало]>0)*(Цены_инертные[Начало]<=[@[Начало])*((Цены_инертные[Окончание]="""")" & _
        "*99999+Цены_инертные[Окончание]>=[@Окончание]))-1,)*Списание[@[Цемент]:[Криопласт]]" & _
        "/1000^(COLUMN(Списание[@[Цемент]:[Криопласт]])<COLUMN([@[СП-3]]))),)"
'очищаем столбец от формул и оставляем лишь значение
    Range("Списание[Себестоимость]").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub
[/vba]
Полную ересь написал сам сижу читаю и понимаю.... :'(
посидел на гугл помощь и понимаю что ни чего я объявлять переменную не обязан, но могу если хочу
тогда можно было так написать код
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
'при изменении в столбце 1
If Target.Column = 1 Then
  'расчет в таблице макросом (на листе: "1С списание")
    Range("Списание[Себестоимость]").FormulaR1C1 = _
        "=IFERROR(SUMPRODUCT(INDEX(Цены_инертные[[Цемент]:[Криопласт]],SUMPRODUCT(ROW(Цены_инертные[Начало])" & _
        "*(Цены_инертные[Начало]>0)*(Цены_инертные[Начало]<=[@[Начало])*((Цены_инертные[Окончание]="""")" & _
        "*99999+Цены_инертные[Окончание]>=[@Окончание]))-1,)*Списание[@[Цемент]:[Криопласт]]" & _
        "/1000^(COLUMN(Списание[@[Цемент]:[Криопласт]])<COLUMN([@[СП-3]]))),)"
'очищаем столбец от формул и оставляем лишь значение
    Range("Списание[Себестоимость]").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    End If
End Sub
[/vba]
НО, увы ругается (выделяя желтым цветом формулы в коде) :'(


Кто бы ты ни был, мир в твоих руках

Сообщение отредактировал lebensvoll - Среда, 31.10.2018, 11:10
 
Ответить
СообщениеStoTisteg, спасибо огромное за ответ!
я теряюсь в дальнейшем (((( прекрасно осознаю что
1.данный код нужно прописать в самом листе
2. Worksheet_Change (Событие изменения содержимого ячейки)
3. дальше ведь я должен объявить эти переменные %) через Dim (вроде бы)
затем лишь код формулы
и после уже очищение столбца от формул и оставляем значение
Получается так (((( но увы
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("Списание[Себестоимость]") Then
  'расчет в таблице макросом (на листе: "1С списание")
    Range("Списание[Себестоимость]").FormulaR1C1 = _
        "=IFERROR(SUMPRODUCT(INDEX(Цены_инертные[[Цемент]:[Криопласт]],SUMPRODUCT(ROW(Цены_инертные[Начало])" & _
        "*(Цены_инертные[Начало]>0)*(Цены_инертные[Начало]<=[@[Начало])*((Цены_инертные[Окончание]="""")" & _
        "*99999+Цены_инертные[Окончание]>=[@Окончание]))-1,)*Списание[@[Цемент]:[Криопласт]]" & _
        "/1000^(COLUMN(Списание[@[Цемент]:[Криопласт]])<COLUMN([@[СП-3]]))),)"
'очищаем столбец от формул и оставляем лишь значение
    Range("Списание[Себестоимость]").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub
[/vba]
Полную ересь написал сам сижу читаю и понимаю.... :'(
посидел на гугл помощь и понимаю что ни чего я объявлять переменную не обязан, но могу если хочу
тогда можно было так написать код
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
'при изменении в столбце 1
If Target.Column = 1 Then
  'расчет в таблице макросом (на листе: "1С списание")
    Range("Списание[Себестоимость]").FormulaR1C1 = _
        "=IFERROR(SUMPRODUCT(INDEX(Цены_инертные[[Цемент]:[Криопласт]],SUMPRODUCT(ROW(Цены_инертные[Начало])" & _
        "*(Цены_инертные[Начало]>0)*(Цены_инертные[Начало]<=[@[Начало])*((Цены_инертные[Окончание]="""")" & _
        "*99999+Цены_инертные[Окончание]>=[@Окончание]))-1,)*Списание[@[Цемент]:[Криопласт]]" & _
        "/1000^(COLUMN(Списание[@[Цемент]:[Криопласт]])<COLUMN([@[СП-3]]))),)"
'очищаем столбец от формул и оставляем лишь значение
    Range("Списание[Себестоимость]").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    End If
End Sub
[/vba]
НО, увы ругается (выделяя желтым цветом формулы в коде) :'(

Автор - lebensvoll
Дата добавления - 31.10.2018 в 10:44
_Boroda_ Дата: Среда, 31.10.2018, 11:46 | Сообщение № 6
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13502
Репутация: 5526 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Вы даже укоротили формулу в два раза

Нууу, не в два, а почти в пять. "Да кушайте, кто их считает"
решил дополнить Ваш код вот так вот:

Можно проще и быстрее
[vba]
Код
Range("Списание[Себестоимость]")=Range("Списание[Себестоимость]").Value
[/vba]
А проверку изменения нужно вешать не на событие, а на события Worksheet_Change. У нас формула смотрит на две таблицы в двух листах, следовательно, переписывать ее (формулу) нужно при изменениях в этих листах. И не просто в изменениях, а в диапазонах таблиц. Можно повесить на Workbook_SheetChange, но там сложнее получится
И сыму формулу Вы как-то не так переписали.
Короче, в обычном модуле
[vba]
Код
Sub vst1()
    With Range("Списание[Себестоимость]")
        .FormulaR1C1 = _
            "=IFERROR(SUMPRODUCT(INDEX(Цены_инертные[[Цемент]:[Криопласт]],SUMPRODUCT(ROW(Цены_инертные[Начало])" & _
            "*(Цены_инертные[Начало]>0)*(Цены_инертные[Начало]<=[@Начало])*((Цены_инертные[Окончание]="""")" & _
            "*99999+Цены_инертные[Окончание]>=[@Окончание]))-1,)*Списание[@[Цемент]:[Криопласт]]" & _
            "/1000^(COLUMN(Списание[@[Цемент]:[Криопласт]])<COLUMN([@[СП-3]]))),)"
        .Value = .Value
    End With
End Sub
[/vba]
В модуле листа Списание
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("Списание[#All]")) Is Nothing Then
        Application.EnableEvents = 0
        vst1
        Application.EnableEvents = 1
    End If
End Sub
[/vba]
В модуле листа Цены
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("Цены_инертные[#All]")) Is Nothing Then
        Application.EnableEvents = 0
        vst1
        Application.EnableEvents = 1
    End If
End Sub
[/vba]
К сообщению приложен файл: 6191236_2.xlsm(65.1 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Вы даже укоротили формулу в два раза

Нууу, не в два, а почти в пять. "Да кушайте, кто их считает"
решил дополнить Ваш код вот так вот:

Можно проще и быстрее
[vba]
Код
Range("Списание[Себестоимость]")=Range("Списание[Себестоимость]").Value
[/vba]
А проверку изменения нужно вешать не на событие, а на события Worksheet_Change. У нас формула смотрит на две таблицы в двух листах, следовательно, переписывать ее (формулу) нужно при изменениях в этих листах. И не просто в изменениях, а в диапазонах таблиц. Можно повесить на Workbook_SheetChange, но там сложнее получится
И сыму формулу Вы как-то не так переписали.
Короче, в обычном модуле
[vba]
Код
Sub vst1()
    With Range("Списание[Себестоимость]")
        .FormulaR1C1 = _
            "=IFERROR(SUMPRODUCT(INDEX(Цены_инертные[[Цемент]:[Криопласт]],SUMPRODUCT(ROW(Цены_инертные[Начало])" & _
            "*(Цены_инертные[Начало]>0)*(Цены_инертные[Начало]<=[@Начало])*((Цены_инертные[Окончание]="""")" & _
            "*99999+Цены_инертные[Окончание]>=[@Окончание]))-1,)*Списание[@[Цемент]:[Криопласт]]" & _
            "/1000^(COLUMN(Списание[@[Цемент]:[Криопласт]])<COLUMN([@[СП-3]]))),)"
        .Value = .Value
    End With
End Sub
[/vba]
В модуле листа Списание
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("Списание[#All]")) Is Nothing Then
        Application.EnableEvents = 0
        vst1
        Application.EnableEvents = 1
    End If
End Sub
[/vba]
В модуле листа Цены
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("Цены_инертные[#All]")) Is Nothing Then
        Application.EnableEvents = 0
        vst1
        Application.EnableEvents = 1
    End If
End Sub
[/vba]

Автор - _Boroda_
Дата добавления - 31.10.2018 в 11:46
lebensvoll Дата: Среда, 31.10.2018, 12:54 | Сообщение № 7
Группа: Проверенные
Ранг: Ветеран
Сообщений: 828
Репутация: 15 ±
Замечаний: 0% ±

Excel 2010
_Boroda_,
Александр, как я понимаю что два кода на листах в которых мы как раз таки отслеживаем события изменений.
А сам код для работы с формулой остается в модуле
Но даже Ваш файл не дает решения ((((
Если начинаешь вносить изменения
либо в таблицу "Цены на инертные" или же дополняя ее
либо в таблицу "списания" или же дополнения ее новыми списания ми
:'(
вот смотрите приложенный файл где дополняю информацию в этих таблицах но ни чего не происходит.
А хочется сделать так, чтоб активация макроса (обсчет результата значения по себестоимости) срабатывал сразу как только вносятся изменения в таблицах и без нажатия каких либо кнопок и активаций через команды
К сообщению приложен файл: 7179138.xlsm(63.7 Kb)


Кто бы ты ни был, мир в твоих руках
 
Ответить
Сообщение_Boroda_,
Александр, как я понимаю что два кода на листах в которых мы как раз таки отслеживаем события изменений.
А сам код для работы с формулой остается в модуле
Но даже Ваш файл не дает решения ((((
Если начинаешь вносить изменения
либо в таблицу "Цены на инертные" или же дополняя ее
либо в таблицу "списания" или же дополнения ее новыми списания ми
:'(
вот смотрите приложенный файл где дополняю информацию в этих таблицах но ни чего не происходит.
А хочется сделать так, чтоб активация макроса (обсчет результата значения по себестоимости) срабатывал сразу как только вносятся изменения в таблицах и без нажатия каких либо кнопок и активаций через команды

Автор - lebensvoll
Дата добавления - 31.10.2018 в 12:54
_Boroda_ Дата: Среда, 31.10.2018, 13:01 | Сообщение № 8
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13502
Репутация: 5526 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Зачем Вы сделали модуль 3?
хочется сделать так, чтоб активация макроса (обсчет результата значения по себестоимости) срабатывал сразу

Вы не поверите, но я Вам именно так и сделал. Зачем Вы добавили Модуль3 с ЕЩЕ ОДНИМ макросом vst1?


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЗачем Вы сделали модуль 3?
хочется сделать так, чтоб активация макроса (обсчет результата значения по себестоимости) срабатывал сразу

Вы не поверите, но я Вам именно так и сделал. Зачем Вы добавили Модуль3 с ЕЩЕ ОДНИМ макросом vst1?

Автор - _Boroda_
Дата добавления - 31.10.2018 в 13:01
lebensvoll Дата: Среда, 31.10.2018, 13:18 | Сообщение № 9
Группа: Проверенные
Ранг: Ветеран
Сообщений: 828
Репутация: 15 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, тут я признаюсь совершил оплошность ((((
потому как когда решил внести изменения в таблицы или дополнить их информации смотрю ни чего не произошло.
Подумал что скорее всего нужно через вкладку разработчик выбрать нужный макрос, но при его нажатии на выбранный макрос создался модуль 3.
Удалил файл и еще раз скачал Ваш
Цитата
Дата: Среда, 31.10.2018, 11:46 | Сообщение № 6

Изменяя значения цен
[img][/img]
даже решил изменить расход
[img][/img]
но как ответ был 35358,22 так и остался :'(
Я немогу понять почему так.
Я вот уверен что у вас все верно и прописано и работает (а у меня все шиворот на выворот)


Кто бы ты ни был, мир в твоих руках
 
Ответить
Сообщение_Boroda_, тут я признаюсь совершил оплошность ((((
потому как когда решил внести изменения в таблицы или дополнить их информации смотрю ни чего не произошло.
Подумал что скорее всего нужно через вкладку разработчик выбрать нужный макрос, но при его нажатии на выбранный макрос создался модуль 3.
Удалил файл и еще раз скачал Ваш
Цитата
Дата: Среда, 31.10.2018, 11:46 | Сообщение № 6

Изменяя значения цен
[img][/img]
даже решил изменить расход
[img][/img]
но как ответ был 35358,22 так и остался :'(
Я немогу понять почему так.
Я вот уверен что у вас все верно и прописано и работает (а у меня все шиворот на выворот)

Автор - lebensvoll
Дата добавления - 31.10.2018 в 13:18
_Boroda_ Дата: Среда, 31.10.2018, 13:22 | Сообщение № 10
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13502
Репутация: 5526 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
А макросы-то разрешены?


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеА макросы-то разрешены?

Автор - _Boroda_
Дата добавления - 31.10.2018 в 13:22
lebensvoll Дата: Среда, 31.10.2018, 13:33 | Сообщение № 11
Группа: Проверенные
Ранг: Ветеран
Сообщений: 828
Репутация: 15 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, я давно установил этот флажок. Прекрасно осознаю что это чревато но (((( лень каждый раз разрешать (иной раз все срочно и быстро нужно сделать)
[img][/img]


Кто бы ты ни был, мир в твоих руках
 
Ответить
Сообщение_Boroda_, я давно установил этот флажок. Прекрасно осознаю что это чревато но (((( лень каждый раз разрешать (иной раз все срочно и быстро нужно сделать)
[img][/img]

Автор - lebensvoll
Дата добавления - 31.10.2018 в 13:33
_Boroda_ Дата: Среда, 31.10.2018, 13:37 | Сообщение № 12
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13502
Репутация: 5526 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Тогда еще раз файл покажите. Новый


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТогда еще раз файл покажите. Новый

Автор - _Boroda_
Дата добавления - 31.10.2018 в 13:37
lebensvoll Дата: Среда, 31.10.2018, 13:51 | Сообщение № 13
Группа: Проверенные
Ранг: Ветеран
Сообщений: 828
Репутация: 15 ±
Замечаний: 0% ±

Excel 2010
_Boroda_,
вот смотрите скачал вновь Ваш файл
пытаюсь изменить значение в ценах, получаю это
[img][/img]
и начинает ругаться на
[img][/img]
И даже если я пропускаю данную ошибку и начинаю изменять цены то себестоимость не изменяется
К сообщению приложен файл: 8652732.xlsm(63.0 Kb)


Кто бы ты ни был, мир в твоих руках
 
Ответить
Сообщение_Boroda_,
вот смотрите скачал вновь Ваш файл
пытаюсь изменить значение в ценах, получаю это
[img][/img]
и начинает ругаться на
[img][/img]
И даже если я пропускаю данную ошибку и начинаю изменять цены то себестоимость не изменяется

Автор - lebensvoll
Дата добавления - 31.10.2018 в 13:51
_Boroda_ Дата: Среда, 31.10.2018, 15:13 | Сообщение № 14
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13502
Репутация: 5526 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Запишите макрорекодером вставку формулы. Скопируйте оттуда формулу, вставьте на место желтого. Добавьте переносы строк. Попробуйте еще раз что-нибудь поменять в таблице Excel

У меня приложенный Вами файл нормально работает


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЗапишите макрорекодером вставку формулы. Скопируйте оттуда формулу, вставьте на место желтого. Добавьте переносы строк. Попробуйте еще раз что-нибудь поменять в таблице Excel

У меня приложенный Вами файл нормально работает

Автор - _Boroda_
Дата добавления - 31.10.2018 в 15:13
lebensvoll Дата: Четверг, 01.11.2018, 16:55 | Сообщение № 15
Группа: Проверенные
Ранг: Ветеран
Сообщений: 828
Репутация: 15 ±
Замечаний: 0% ±

Excel 2010
_Boroda_,
Александр целый день сижу читаю смотрю ищу пробую на других примерах (подобиях).
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
[/vba]
является событием в любой ячейки на конкретном листе (куда мы его прописываем) тут все понятно
[vba]
Код
If Not Intersect(Target, Range("Цены[#All]")) Is Nothing Then
[/vba]
является определением конкретики (что не на всем листе а именно в умной таблице)
[vba]
Код
Application.EnableEvents = 0
Application.EnableEvents = 1
[/vba]
Тут все ясно включение и выключение цикла события в момент выполнения
В приложенном примере я записал макрос3
[vba]
Код
Sub Макрос3()
' Макрос3 Макрос
        With Range("Списание[Себестоимость]")
        .FormulaR1C1 = _
        "=Цены[[Цена]*Списание[цемент]"
        .Value = .Value
    End With
End Sub
[/vba]
который должен сработать при любых изменениях на двух листах и произвел расчет цемент * цену а после оставил лишь значение
И что вы думаете :'( вновь
НЕ РАБОТАЕТ даже на простом примере...
Может у меня что то не то с экселем.
Не могли бы посмотреть файл во вложении как у вас работает он!?
Люди добрые подскажите в чем могут быть проблемы, почему так происходит
И чт
К сообщению приложен файл: 8603916.xlsm(16.9 Kb)


Кто бы ты ни был, мир в твоих руках

Сообщение отредактировал lebensvoll - Четверг, 01.11.2018, 17:02
 
Ответить
Сообщение_Boroda_,
Александр целый день сижу читаю смотрю ищу пробую на других примерах (подобиях).
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
[/vba]
является событием в любой ячейки на конкретном листе (куда мы его прописываем) тут все понятно
[vba]
Код
If Not Intersect(Target, Range("Цены[#All]")) Is Nothing Then
[/vba]
является определением конкретики (что не на всем листе а именно в умной таблице)
[vba]
Код
Application.EnableEvents = 0
Application.EnableEvents = 1
[/vba]
Тут все ясно включение и выключение цикла события в момент выполнения
В приложенном примере я записал макрос3
[vba]
Код
Sub Макрос3()
' Макрос3 Макрос
        With Range("Списание[Себестоимость]")
        .FormulaR1C1 = _
        "=Цены[[Цена]*Списание[цемент]"
        .Value = .Value
    End With
End Sub
[/vba]
который должен сработать при любых изменениях на двух листах и произвел расчет цемент * цену а после оставил лишь значение
И что вы думаете :'( вновь
НЕ РАБОТАЕТ даже на простом примере...
Может у меня что то не то с экселем.
Не могли бы посмотреть файл во вложении как у вас работает он!?
Люди добрые подскажите в чем могут быть проблемы, почему так происходит
И чт

Автор - lebensvoll
Дата добавления - 01.11.2018 в 16:55
_Boroda_ Дата: Четверг, 01.11.2018, 17:17 | Сообщение № 16
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13502
Репутация: 5526 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
"=Цены[[Цена]*Списание[цемент]"

Откуда Вы это взяли? Только не говорите, что макрорекодер так записал, все равно не поверю
Должно быть вот так
[vba]
Код
"=Цены[Цена]*Списание[цемент]"
[/vba]


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
"=Цены[[Цена]*Списание[цемент]"

Откуда Вы это взяли? Только не говорите, что макрорекодер так записал, все равно не поверю
Должно быть вот так
[vba]
Код
"=Цены[Цена]*Списание[цемент]"
[/vba]

Автор - _Boroda_
Дата добавления - 01.11.2018 в 17:17
lebensvoll Дата: Четверг, 01.11.2018, 17:24 | Сообщение № 17
Группа: Проверенные
Ранг: Ветеран
Сообщений: 828
Репутация: 15 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, нет вы правы я в начале записал смотрю не работает решил использовать подобие вашего примера :'( и похоже совершаю ошибку
Блиииииин и что Вы думаете!?
Исправил и все работает :o
Сейчас тогда попробую применить другой метод по варианту
Цитата
Запишите макрорекодером вставку формулы. Скопируйте оттуда формулу, вставьте на место желтого. Добавьте переносы строк. Попробуйте еще раз что-нибудь поменять в таблице Excel

Но сделать иначе не копировать а именно прописать и посмотреть что получится тогда
ПОЛУЧИЛОСЬ!!!!
НО, заметил разницу при написании и копировании ( с чем это связано не могу понять)
Вот Ваш код:
[vba]
Код
Sub vst1()
    With Range("Списание[Себестоимость]")
        .FormulaR1C1 = _
            "=IFERROR(SUMPRODUCT(INDEX(Цены_инертные[[Цемент]:[Криопласт]],SUMPRODUCT(ROW(Цены_инертные[Начало])" & _
            "*(Цены_инертные[Начало]>0)*(Цены_инертные[Начало]<=[@Начало])*((Цены_инертные[Окончание]="""")" & _
            "*99999+Цены_инертные[Окончание]>=[@Окончание]))-1,)*Списание[@[Цемент]:[Криопласт]]" & _
            "/1000^(COLUMN(Списание[@[Цемент]:[Криопласт]])<COLUMN([@[СП-3]]))),)"
        .Value = .Value
    End With
End Sub
[/vba]
А вот код когда начинаешь прописывать
[vba]
Код
Sub vst1()
    With Range("Списание[Себестоимость]")
        .FormulaR1C1 = _
        "=IFERROR(SUMPRODUCT(INDEX(Цены_инертные[[Цемент]:[Криопласт]],SUMPRODUCT(ROW(Цены_инертные[Начало])" & _
        "*(Цены_инертные[Начало]>0)*(Цены_инертные[Начало]<=Списание[[#This Row],[Начало]])*((Цены_инертные[Окончание]="""")" & _
        "*99999+Цены_инертные[Окончание]>=Списание[[#This Row],[Окончание]]))-1,)*Списание[[#This Row],[Цемент]:[Криопласт]]" & _
        "/1000*(COLUMN(Списание[[#This Row],[Цемент]:[Криопласт]])<COLUMN(Списание[[#This Row],[СП-3]]))),)"
        .Value = .Value
    End With
End Sub
[/vba]
И разница лишь в вот в этом %)
Цитата
[#This Row]

СПАСИБО ОГРОМНЕЙШЕЕ ВАМ
за то я еще и еще раз пропустил через себя всю информацию что означает то то и то то. Пусть не без ошибок но все равно


Кто бы ты ни был, мир в твоих руках

Сообщение отредактировал lebensvoll - Четверг, 01.11.2018, 17:40
 
Ответить
Сообщение_Boroda_, нет вы правы я в начале записал смотрю не работает решил использовать подобие вашего примера :'( и похоже совершаю ошибку
Блиииииин и что Вы думаете!?
Исправил и все работает :o
Сейчас тогда попробую применить другой метод по варианту
Цитата
Запишите макрорекодером вставку формулы. Скопируйте оттуда формулу, вставьте на место желтого. Добавьте переносы строк. Попробуйте еще раз что-нибудь поменять в таблице Excel

Но сделать иначе не копировать а именно прописать и посмотреть что получится тогда
ПОЛУЧИЛОСЬ!!!!
НО, заметил разницу при написании и копировании ( с чем это связано не могу понять)
Вот Ваш код:
[vba]
Код
Sub vst1()
    With Range("Списание[Себестоимость]")
        .FormulaR1C1 = _
            "=IFERROR(SUMPRODUCT(INDEX(Цены_инертные[[Цемент]:[Криопласт]],SUMPRODUCT(ROW(Цены_инертные[Начало])" & _
            "*(Цены_инертные[Начало]>0)*(Цены_инертные[Начало]<=[@Начало])*((Цены_инертные[Окончание]="""")" & _
            "*99999+Цены_инертные[Окончание]>=[@Окончание]))-1,)*Списание[@[Цемент]:[Криопласт]]" & _
            "/1000^(COLUMN(Списание[@[Цемент]:[Криопласт]])<COLUMN([@[СП-3]]))),)"
        .Value = .Value
    End With
End Sub
[/vba]
А вот код когда начинаешь прописывать
[vba]
Код
Sub vst1()
    With Range("Списание[Себестоимость]")
        .FormulaR1C1 = _
        "=IFERROR(SUMPRODUCT(INDEX(Цены_инертные[[Цемент]:[Криопласт]],SUMPRODUCT(ROW(Цены_инертные[Начало])" & _
        "*(Цены_инертные[Начало]>0)*(Цены_инертные[Начало]<=Списание[[#This Row],[Начало]])*((Цены_инертные[Окончание]="""")" & _
        "*99999+Цены_инертные[Окончание]>=Списание[[#This Row],[Окончание]]))-1,)*Списание[[#This Row],[Цемент]:[Криопласт]]" & _
        "/1000*(COLUMN(Списание[[#This Row],[Цемент]:[Криопласт]])<COLUMN(Списание[[#This Row],[СП-3]]))),)"
        .Value = .Value
    End With
End Sub
[/vba]
И разница лишь в вот в этом %)
Цитата
[#This Row]

СПАСИБО ОГРОМНЕЙШЕЕ ВАМ
за то я еще и еще раз пропустил через себя всю информацию что означает то то и то то. Пусть не без ошибок но все равно

Автор - lebensvoll
Дата добавления - 01.11.2018 в 17:24
lebensvoll Дата: Пятница, 02.11.2018, 09:36 | Сообщение № 18
Группа: Проверенные
Ранг: Ветеран
Сообщений: 828
Репутация: 15 ±
Замечаний: 0% ±

Excel 2010
%) в продолжении темы но на другом листе :'(
На листе "ОТГРУЗКА" имеется диапазон (А1:В16) который является (как бы формой) - При внесении данных в нужные ячейки и нажатии кнопки "Внести запись" в таблицу "ОТГРУЗКА" вносятся эти данные:

а это код "формы"

Решил аналогично уйти от формул с таблицы "ОТГРУЗКА" как с таблицы "СПИСАНИЕ в столбце "Себестоимость""
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("Отгрузка[#All]")) Is Nothing Then
        Application.EnableEvents = 0
        vst2
        Application.EnableEvents = 1
    End If
End Sub
[/vba]
Ну и соответственно код для таблицы "ОТГРУЗКА" столбец "Стоимость"
[vba]
Код
Sub vst2() 'Макрос для расчета стоимости на листе ОТГРУЗКА
    With Range("Отгрузка[Стоимость]")
        .FormulaR1C1 = _
        "=SUMPRODUCT(Спецификация[Цена]*(Спецификация[Продукция]=Отгрузка[[#This Row],[Продукция]])" & _
        "*(Спецификация[Контрагент]=Отгрузка[[#This Row],[Контрагент]])*(Спецификация[Начало]<=Отгрузка[[#This Row],[Дата]])" & _
        "*((Спецификация[Окончание]="""")*99999+Спецификация[Окончание]>=Отгрузка[[#This Row],[Дата]]))"
        .Value = .Value
    End With
End Sub
[/vba]
:'( код для добавления данных в таблицу срабатывает но вот расчет "Стоимости" :'( нет.
В чем моя ошибка!?
Просто хочу по аналогии убрать формулы со всей этой таблицы и с других столбцов при добавлении новой записи в таблицу.
Мне кажется что
Цитата
Add_Sell() 'Макрос для "формы" на листе ОТГРУЗКА

я должен дополнить код для расчета столбца "Стоимость", если я верно понимаю
К сообщению приложен файл: 6936996.xlsm(75.2 Kb)


Кто бы ты ни был, мир в твоих руках
 
Ответить
Сообщение%) в продолжении темы но на другом листе :'(
На листе "ОТГРУЗКА" имеется диапазон (А1:В16) который является (как бы формой) - При внесении данных в нужные ячейки и нажатии кнопки "Внести запись" в таблицу "ОТГРУЗКА" вносятся эти данные:

а это код "формы"

Решил аналогично уйти от формул с таблицы "ОТГРУЗКА" как с таблицы "СПИСАНИЕ в столбце "Себестоимость""
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("Отгрузка[#All]")) Is Nothing Then
        Application.EnableEvents = 0
        vst2
        Application.EnableEvents = 1
    End If
End Sub
[/vba]
Ну и соответственно код для таблицы "ОТГРУЗКА" столбец "Стоимость"
[vba]
Код
Sub vst2() 'Макрос для расчета стоимости на листе ОТГРУЗКА
    With Range("Отгрузка[Стоимость]")
        .FormulaR1C1 = _
        "=SUMPRODUCT(Спецификация[Цена]*(Спецификация[Продукция]=Отгрузка[[#This Row],[Продукция]])" & _
        "*(Спецификация[Контрагент]=Отгрузка[[#This Row],[Контрагент]])*(Спецификация[Начало]<=Отгрузка[[#This Row],[Дата]])" & _
        "*((Спецификация[Окончание]="""")*99999+Спецификация[Окончание]>=Отгрузка[[#This Row],[Дата]]))"
        .Value = .Value
    End With
End Sub
[/vba]
:'( код для добавления данных в таблицу срабатывает но вот расчет "Стоимости" :'( нет.
В чем моя ошибка!?
Просто хочу по аналогии убрать формулы со всей этой таблицы и с других столбцов при добавлении новой записи в таблицу.
Мне кажется что
Цитата
Add_Sell() 'Макрос для "формы" на листе ОТГРУЗКА

я должен дополнить код для расчета столбца "Стоимость", если я верно понимаю

Автор - lebensvoll
Дата добавления - 02.11.2018 в 09:36
_Boroda_ Дата: Пятница, 02.11.2018, 09:53 | Сообщение № 19
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13502
Репутация: 5526 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Да, намутили Вы там...
Что нужно не совсем понятно. Так?
[vba]
Код
Private Sub CommandButton1_Click()
Application.EnableEvents = 0
    Call Add_Sell
    vst2
Application.EnableEvents = 1
End Sub
[/vba]


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеДа, намутили Вы там...
Что нужно не совсем понятно. Так?
[vba]
Код
Private Sub CommandButton1_Click()
Application.EnableEvents = 0
    Call Add_Sell
    vst2
Application.EnableEvents = 1
End Sub
[/vba]

Автор - _Boroda_
Дата добавления - 02.11.2018 в 09:53
lebensvoll Дата: Пятница, 02.11.2018, 11:43 | Сообщение № 20
Группа: Проверенные
Ранг: Ветеран
Сообщений: 828
Репутация: 15 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, %)
Цитата
Да, намутили Вы там...

Блииин СОГЛАСЕН!!!
Т.е. получается что мне не нужно было писать этот код

А лишь дополнить уже существующий на странице вторым макросом :o
Спасибо Вам огромное!!!
Я даже неверно думал
Цитата
Мне кажется что
Цитата
Add_Sell() 'Макрос для "формы" на листе ОТГРУЗКА

я должен дополнить код для расчета столбца "Стоимость", если я верно понимаю

_Boroda_, а как тогда продолжить вычисления в других столбцах в коде vst2!? как я могу прописать через If в продолжение следующего расчета на столбце или же после End With
[vba]
Код
        With Range("Отгрузка[ИТОГО]")
        .FormulaR1C1 = _
        "=Отгрузка[[#This Row],[Количество]]*Отгрузка[[#This Row],[Стоимость]]" & _
        "+Отгрузка[[#This Row],[Доставка]]*Отгрузка[[#This Row]"
        .Value = .Value
    End With
End Sub
[/vba]
Спасибо огромное еще раз


Кто бы ты ни был, мир в твоих руках
 
Ответить
Сообщение_Boroda_, %)
Цитата
Да, намутили Вы там...

Блииин СОГЛАСЕН!!!
Т.е. получается что мне не нужно было писать этот код

А лишь дополнить уже существующий на странице вторым макросом :o
Спасибо Вам огромное!!!
Я даже неверно думал
Цитата
Мне кажется что
Цитата
Add_Sell() 'Макрос для "формы" на листе ОТГРУЗКА

я должен дополнить код для расчета столбца "Стоимость", если я верно понимаю

_Boroda_, а как тогда продолжить вычисления в других столбцах в коде vst2!? как я могу прописать через If в продолжение следующего расчета на столбце или же после End With
[vba]
Код
        With Range("Отгрузка[ИТОГО]")
        .FormulaR1C1 = _
        "=Отгрузка[[#This Row],[Количество]]*Отгрузка[[#This Row],[Стоимость]]" & _
        "+Отгрузка[[#This Row],[Доставка]]*Отгрузка[[#This Row]"
        .Value = .Value
    End With
End Sub
[/vba]
Спасибо огромное еще раз

Автор - lebensvoll
Дата добавления - 02.11.2018 в 11:43
Мир MS Excel » Вопросы и решения » Вопросы по VBA » В место формулы макрос (Макросы/Sub)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

Яндекс цитирования
© 2010-2018 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!