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

Вход

Регистрация

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

 

= Мир MS Excel/Как макросом копировать формулы с подстановкой в них текущих - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Как макросом копировать формулы с подстановкой в них текущих (Макросы/Sub)
Как макросом копировать формулы с подстановкой в них текущих
Avgust1 Дата: Четверг, 11.04.2024, 08:58 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Добрый день.
Есть такая формула : [vba]
Код
=ИНДЕКС(План!$G$2:$G$40;АГРЕГАТ(15;6;(СТРОКА(План!$D$2:$D$40)-СТРОКА(План!$D$2)+1)/(План!$D$2:$D$40=" & ws.Cells(i, "A").Value & ws.Cells(i, "E").Value & ");1))
[/vba],

я ее пытаюсь вставить в иные ячейки циклом

[vba]
Код
Set sourceCell.Formula = "=ИНДЕКС(План!$G$2:$G$40;АГРЕГАТ(15;6;(СТРОКА(План!$D$2:$D$40)-СТРОКА(План!$D$2)+1)/(План!$D$2:$D$40=" & ws.Cells(i, "A").Value & ws.Cells(i, "E").Value & ");1))"
Set destinationCell = Cells(i, "F")
destinationCell.Formula = sourceCell.Formula
[/vba]

Далее вставка вторым циклом:
[vba]
Код
Set destinationCell = Cells(i + j, "F")
[/vba]
Но макрос останавливается и подчеркивает присвоение переменной желтой заливкой.

Пробовал другой вариант если скопировать формулу из ячейки с формулой:

[vba]
Код
Set sourceCell = Cells(i - 1, "F")
Set destinationCell = Cells(i, "F")
destinationCell.Formula = sourceCell.Formula
[/vba]

и далее вставка циклом:
Далее вставка вторым циклом:
[vba]
Код
Set destinationCell = Cells(i + j, "F")
[/vba]

так ошибку не выдает, но все формулы ссылаются на первую строку (i-1) откуда была скопирована формула, а не на текущие строки (i+j)
Прошу помочь, что я делаю не так?
 
Ответить
СообщениеДобрый день.
Есть такая формула : [vba]
Код
=ИНДЕКС(План!$G$2:$G$40;АГРЕГАТ(15;6;(СТРОКА(План!$D$2:$D$40)-СТРОКА(План!$D$2)+1)/(План!$D$2:$D$40=" & ws.Cells(i, "A").Value & ws.Cells(i, "E").Value & ");1))
[/vba],

я ее пытаюсь вставить в иные ячейки циклом

[vba]
Код
Set sourceCell.Formula = "=ИНДЕКС(План!$G$2:$G$40;АГРЕГАТ(15;6;(СТРОКА(План!$D$2:$D$40)-СТРОКА(План!$D$2)+1)/(План!$D$2:$D$40=" & ws.Cells(i, "A").Value & ws.Cells(i, "E").Value & ");1))"
Set destinationCell = Cells(i, "F")
destinationCell.Formula = sourceCell.Formula
[/vba]

Далее вставка вторым циклом:
[vba]
Код
Set destinationCell = Cells(i + j, "F")
[/vba]
Но макрос останавливается и подчеркивает присвоение переменной желтой заливкой.

Пробовал другой вариант если скопировать формулу из ячейки с формулой:

[vba]
Код
Set sourceCell = Cells(i - 1, "F")
Set destinationCell = Cells(i, "F")
destinationCell.Formula = sourceCell.Formula
[/vba]

и далее вставка циклом:
Далее вставка вторым циклом:
[vba]
Код
Set destinationCell = Cells(i + j, "F")
[/vba]

так ошибку не выдает, но все формулы ссылаются на первую строку (i-1) откуда была скопирована формула, а не на текущие строки (i+j)
Прошу помочь, что я делаю не так?

Автор - Avgust1
Дата добавления - 11.04.2024 в 08:58
Nic70y Дата: Четверг, 11.04.2024, 09:33 | Сообщение № 2
Группа: Друзья
Ранг: Экселист
Сообщений: 8791
Репутация: 2293 ±
Замечаний: 0% ±

Excel 2010
Avgust1, приложите пжл файл


ЮMoney 41001841029809
 
Ответить
СообщениеAvgust1, приложите пжл файл

Автор - Nic70y
Дата добавления - 11.04.2024 в 09:33
китин Дата: Четверг, 11.04.2024, 10:44 | Сообщение № 3
Группа: Модераторы
Ранг: Экселист
Сообщений: 7019
Репутация: 1074 ±
Замечаний: 0% ±

Excel 2007;2010;2016
Avgust1, будьте так любезны. прочитайте правила форума и оформите ваш пост в соответствии с ними.
Помогающим: просьба не отвечать до устранения нарушений


Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
СообщениеAvgust1, будьте так любезны. прочитайте правила форума и оформите ваш пост в соответствии с ними.
Помогающим: просьба не отвечать до устранения нарушений

Автор - китин
Дата добавления - 11.04.2024 в 10:44
Avgust1 Дата: Четверг, 11.04.2024, 11:49 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Nic70y, Добрый день. Не совсем понял, какой файл приложить? Всего проекта, или только конкретный макрос в текстовом виде?
 
Ответить
СообщениеNic70y, Добрый день. Не совсем понял, какой файл приложить? Всего проекта, или только конкретный макрос в текстовом виде?

Автор - Avgust1
Дата добавления - 11.04.2024 в 11:49
Avgust1 Дата: Четверг, 11.04.2024, 11:51 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

китин, Здравствуйте уважаемый. Не совсем понял, что не так с моим постом, но с удовольствием его исправлю согласно ваших требований. Где можно ознакомиться с этими требованиями? Не нашел ссылку на правила.
 
Ответить
Сообщениекитин, Здравствуйте уважаемый. Не совсем понял, что не так с моим постом, но с удовольствием его исправлю согласно ваших требований. Где можно ознакомиться с этими требованиями? Не нашел ссылку на правила.

Автор - Avgust1
Дата добавления - 11.04.2024 в 11:51
Nic70y Дата: Четверг, 11.04.2024, 11:59 | Сообщение № 6
Группа: Друзья
Ранг: Экселист
Сообщений: 8791
Репутация: 2293 ±
Замечаний: 0% ±

Excel 2010
Avgust1, Ваша формула ссылается на значения столбцов A и E, хочется видеть эти ячейки, можно не все, а также лист и диапазон План!$D$2:$D$40
Не нашел ссылку на правила.

http://www.excelworld.ru/forum/2-20-1
там и файле есть


ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Четверг, 11.04.2024, 12:01
 
Ответить
СообщениеAvgust1, Ваша формула ссылается на значения столбцов A и E, хочется видеть эти ячейки, можно не все, а также лист и диапазон План!$D$2:$D$40
Не нашел ссылку на правила.

http://www.excelworld.ru/forum/2-20-1
там и файле есть

Автор - Nic70y
Дата добавления - 11.04.2024 в 11:59
Avgust1 Дата: Четверг, 11.04.2024, 13:12 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Nic70y, Спасибо за отклик, прилагаю образец файла, код который не получается есть в закомментированном виде в макросе "Перенос_дат_сортировка"
К сообщению приложен файл: fajl_primer.xlsm (144.2 Kb)
 
Ответить
СообщениеNic70y, Спасибо за отклик, прилагаю образец файла, код который не получается есть в закомментированном виде в макросе "Перенос_дат_сортировка"

Автор - Avgust1
Дата добавления - 11.04.2024 в 13:12
Gustav Дата: Четверг, 11.04.2024, 13:27 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2757
Репутация: 1139 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Set sourceCell.Formula = "=ИНДЕКС(План!$G$2:$G$40;АГРЕГАТ(15;6;(СТРОКА(План!$D$2:$D$40)-СТРОКА(План!$D$2)+1)/(План!$D$2:$D$40=" & ws.Cells(i, "A").Value & ws.Cells(i, "E").Value & ");1))"

Навскидку: в этой формуле не хватает кавычек и Set в начале не нужен:
[vba]
Код
sourceCell.FormulaLocal = "=ИНДЕКС(План!$G$2:$G$40;АГРЕГАТ(15;6;(СТРОКА(План!$D$2:$D$40)-СТРОКА(План!$D$2)+1)/(План!$D$2:$D$40=""" & ws.Cells(i, "A").Value & ws.Cells(i, "E").Value & """);1))"
[/vba]Обратите внимание на места в формуле, где стало по три кавычки подряд """ вместо одной ".

[p.s.]И как совершенно справедливо указал mgt ниже в сообщении № 10:[/p.s.]
формулалокал нужна

вместо свойства .Formula нужно использовать .FormulaLocal (обновил выше) - потому что в формуле русские названия функций.


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Четверг, 11.04.2024, 17:57
 
Ответить
Сообщение
Set sourceCell.Formula = "=ИНДЕКС(План!$G$2:$G$40;АГРЕГАТ(15;6;(СТРОКА(План!$D$2:$D$40)-СТРОКА(План!$D$2)+1)/(План!$D$2:$D$40=" & ws.Cells(i, "A").Value & ws.Cells(i, "E").Value & ");1))"

Навскидку: в этой формуле не хватает кавычек и Set в начале не нужен:
[vba]
Код
sourceCell.FormulaLocal = "=ИНДЕКС(План!$G$2:$G$40;АГРЕГАТ(15;6;(СТРОКА(План!$D$2:$D$40)-СТРОКА(План!$D$2)+1)/(План!$D$2:$D$40=""" & ws.Cells(i, "A").Value & ws.Cells(i, "E").Value & """);1))"
[/vba]Обратите внимание на места в формуле, где стало по три кавычки подряд """ вместо одной ".

[p.s.]И как совершенно справедливо указал mgt ниже в сообщении № 10:[/p.s.]
формулалокал нужна

вместо свойства .Formula нужно использовать .FormulaLocal (обновил выше) - потому что в формуле русские названия функций.

Автор - Gustav
Дата добавления - 11.04.2024 в 13:27
Nic70y Дата: Четверг, 11.04.2024, 14:01 | Сообщение № 9
Группа: Друзья
Ранг: Экселист
Сообщений: 8791
Репутация: 2293 ±
Замечаний: 0% ±

Excel 2010
Avgust1, китин, он же модератор писал о тегах,
в режиме редактирования 1го поста, выделяйте текст макроса и жмите кнопку #

в Вашем файле ни чего не понял,
хотите вставлять формулу только напротив желтых ячеек, а остальные строки хотите удалить?
формула по-моему перемудрена


ЮMoney 41001841029809
 
Ответить
СообщениеAvgust1, китин, он же модератор писал о тегах,
в режиме редактирования 1го поста, выделяйте текст макроса и жмите кнопку #

в Вашем файле ни чего не понял,
хотите вставлять формулу только напротив желтых ячеек, а остальные строки хотите удалить?
формула по-моему перемудрена

Автор - Nic70y
Дата добавления - 11.04.2024 в 14:01
mgt Дата: Четверг, 11.04.2024, 16:38 | Сообщение № 10
Группа: Пользователи
Ранг: Участник
Сообщений: 98
Репутация: 25 ±
Замечаний: 0% ±

Excel 2010
По идее еще формулалокал нужна
 
Ответить
СообщениеПо идее еще формулалокал нужна

Автор - mgt
Дата добавления - 11.04.2024 в 16:38
Nic70y Дата: Пятница, 12.04.2024, 08:11 | Сообщение № 11
Группа: Друзья
Ранг: Экселист
Сообщений: 8791
Репутация: 2293 ±
Замечаний: 0% ±

Excel 2010
Avgust1, если я правильно понял, вариант:
[vba]
Код
Sub u_16()
    'Определяем последнюю заполненную строку в столбце A
    a = Cells(Rows.Count, "a").End(xlUp).Row
    'цикл от последней до 4 строки
    For b = a To 4 Step -1
        'если текущая ячейка в столбце A имеет желтую заливку
        If Range("a" & b).Interior.Color = RGB(255, 255, 153) Then
            'значение столбцов A & E текущей строки
            c = Range("a" & b).Value & Range("e" & b).Value
            'Определяем последнюю заполненную строку в столбце D листа План
            d = Sheets("План").Cells(Rows.Count, "d").End(xlUp).Row
            'аналог =ПОИСКПОЗ()
            e = Application.Match(c, Sheets("План").Range("d2:d" & d), 0)
            'аналог =ЕЧИСЛО()
            If IsNumeric(e) Then
                Range("f" & b) = Sheets("План").Range("g" & e + 1).Value
            End If
        End If
        'далее можно условие удаления строки в этом же цикле
    Next
End Sub
[/vba]
К сообщению приложен файл: 48.xlsm (45.4 Kb)


ЮMoney 41001841029809
 
Ответить
СообщениеAvgust1, если я правильно понял, вариант:
[vba]
Код
Sub u_16()
    'Определяем последнюю заполненную строку в столбце A
    a = Cells(Rows.Count, "a").End(xlUp).Row
    'цикл от последней до 4 строки
    For b = a To 4 Step -1
        'если текущая ячейка в столбце A имеет желтую заливку
        If Range("a" & b).Interior.Color = RGB(255, 255, 153) Then
            'значение столбцов A & E текущей строки
            c = Range("a" & b).Value & Range("e" & b).Value
            'Определяем последнюю заполненную строку в столбце D листа План
            d = Sheets("План").Cells(Rows.Count, "d").End(xlUp).Row
            'аналог =ПОИСКПОЗ()
            e = Application.Match(c, Sheets("План").Range("d2:d" & d), 0)
            'аналог =ЕЧИСЛО()
            If IsNumeric(e) Then
                Range("f" & b) = Sheets("План").Range("g" & e + 1).Value
            End If
        End If
        'далее можно условие удаления строки в этом же цикле
    Next
End Sub
[/vba]

Автор - Nic70y
Дата добавления - 12.04.2024 в 08:11
Avgust1 Дата: Суббота, 13.04.2024, 12:24 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Извините, видимо я очень коряво объяснил задачу, попробую сформулировать по другому:
есть формула скопированная из 334 строки столбца G листа данные
"=ИНДЕКС(План!$F$2:$F$40;АГРЕГАТ(15;6;(СТРОКА(План!$D$2:$D$40)-СТРОКА(План!$D$2)+1)/(План!$D$2:$D$40=Данные!B334&Данные!F334);1))",
которая содержится к каждой ячейке столбца G листа Данные.
При добавлении новых строк с данными в лист Данные (после 334 строки) с пустой ячейкой в столбце G, мне необходимо обработать макросом новые строки пересчитывая данные, в том числе скопировать эту формулу из предыдущей строки (т.е. 334-й) и вставить ее в строку 335 и далее (пока не закончатся строки с добавленными данными).
При вставки в новую строку (напр. 335-ю), формула должна теперь брать данные из 335 строки, т.е. выглядеть так:
"=ИНДЕКС(План!$F$2:$F$40;АГРЕГАТ(15;6;(СТРОКА(План!$D$2:$D$40)-СТРОКА(План!$D$2)+1)/(План!$D$2:$D$40=Данные!B335&Данные!F335);1))".
Конечно самый простой вариант, просто протянуть формулу на новые строки, но это не наш метод :))
Как это сделать через макрос?
К сообщению приложен файл: 5862019.xlsm (144.2 Kb)
 
Ответить
СообщениеИзвините, видимо я очень коряво объяснил задачу, попробую сформулировать по другому:
есть формула скопированная из 334 строки столбца G листа данные
"=ИНДЕКС(План!$F$2:$F$40;АГРЕГАТ(15;6;(СТРОКА(План!$D$2:$D$40)-СТРОКА(План!$D$2)+1)/(План!$D$2:$D$40=Данные!B334&Данные!F334);1))",
которая содержится к каждой ячейке столбца G листа Данные.
При добавлении новых строк с данными в лист Данные (после 334 строки) с пустой ячейкой в столбце G, мне необходимо обработать макросом новые строки пересчитывая данные, в том числе скопировать эту формулу из предыдущей строки (т.е. 334-й) и вставить ее в строку 335 и далее (пока не закончатся строки с добавленными данными).
При вставки в новую строку (напр. 335-ю), формула должна теперь брать данные из 335 строки, т.е. выглядеть так:
"=ИНДЕКС(План!$F$2:$F$40;АГРЕГАТ(15;6;(СТРОКА(План!$D$2:$D$40)-СТРОКА(План!$D$2)+1)/(План!$D$2:$D$40=Данные!B335&Данные!F335);1))".
Конечно самый простой вариант, просто протянуть формулу на новые строки, но это не наш метод :))
Как это сделать через макрос?

Автор - Avgust1
Дата добавления - 13.04.2024 в 12:24
Nic70y Дата: Суббота, 13.04.2024, 13:18 | Сообщение № 13
Группа: Друзья
Ранг: Экселист
Сообщений: 8791
Репутация: 2293 ±
Замечаний: 0% ±

Excel 2010
Avgust1, ну так записать ее рекордером и не мучатся
[vba]
Код
ws.Cells(i, "G").FormulaR1C1 = "=INDEX(План!R2C7:R40C7,AGGREGATE(15,6,(ROW(План!R2C4:R40C4)-ROW(План!R2C4)+1)/(План!R2C4:R40C4=Данные!RC[-5]&Данные!RC[-1]),1))"
[/vba]

[p.s.]формулы тоже нужно сувать в тэги, только в свои fx, отредактируйте пжл[/p.s.]


ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Суббота, 13.04.2024, 13:22
 
Ответить
СообщениеAvgust1, ну так записать ее рекордером и не мучатся
[vba]
Код
ws.Cells(i, "G").FormulaR1C1 = "=INDEX(План!R2C7:R40C7,AGGREGATE(15,6,(ROW(План!R2C4:R40C4)-ROW(План!R2C4)+1)/(План!R2C4:R40C4=Данные!RC[-5]&Данные!RC[-1]),1))"
[/vba]

[p.s.]формулы тоже нужно сувать в тэги, только в свои fx, отредактируйте пжл[/p.s.]

Автор - Nic70y
Дата добавления - 13.04.2024 в 13:18
Gustav Дата: Суббота, 13.04.2024, 15:12 | Сообщение № 14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2757
Репутация: 1139 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
просто протянуть формулу на новые строки, но это не наш метод :)) Как это сделать через макрос?

Например, так можно:
[vba]
Код
Sub fillFormulaInNewRows()
    Dim rng1 As Range, rngCurr As Range, rngDest As Range
    Set rng1 = Cells(Rows.Count, "G").End(xlUp)
    Set rngCurr = rng1.CurrentRegion
    Set rngDest = rng1.Resize(rngCurr.Row + rngCurr.Rows.Count - rng1.Row)
    rng1.AutoFill rngDest
End Sub
[/vba]

P.S. Кстати, по поводу "протянуть". Необязательно же тянуть путем цепляния правого нижнего угла ячейки-источника и буквального протягивания вниз на нужное количество строк при зажатой левой кнопке мыши. Достаточно просто дабл-кликнуть правый нижний угол ячейки (в тот момент, когда курсор мыши превращается в крестик при наведении на угол). И вот этот дабл-клик мне представляется даже более быстрым, чем запуск макроса по кнопке или по Alt+F8.


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Суббота, 13.04.2024, 15:26
 
Ответить
Сообщение
просто протянуть формулу на новые строки, но это не наш метод :)) Как это сделать через макрос?

Например, так можно:
[vba]
Код
Sub fillFormulaInNewRows()
    Dim rng1 As Range, rngCurr As Range, rngDest As Range
    Set rng1 = Cells(Rows.Count, "G").End(xlUp)
    Set rngCurr = rng1.CurrentRegion
    Set rngDest = rng1.Resize(rngCurr.Row + rngCurr.Rows.Count - rng1.Row)
    rng1.AutoFill rngDest
End Sub
[/vba]

P.S. Кстати, по поводу "протянуть". Необязательно же тянуть путем цепляния правого нижнего угла ячейки-источника и буквального протягивания вниз на нужное количество строк при зажатой левой кнопке мыши. Достаточно просто дабл-кликнуть правый нижний угол ячейки (в тот момент, когда курсор мыши превращается в крестик при наведении на угол). И вот этот дабл-клик мне представляется даже более быстрым, чем запуск макроса по кнопке или по Alt+F8.

Автор - Gustav
Дата добавления - 13.04.2024 в 15:12
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Как макросом копировать формулы с подстановкой в них текущих (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

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