Как макросом копировать формулы с подстановкой в них текущих

Дата: Четверг, 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)
Прошу помочь, что я делаю не так?

Дата: Четверг, 11.04.2024, 09:33 | Сообщение № 2

Группа: Друзья

Ранг: Экселист

Сообщений: 8761

Репутация: 2273 ±

Замечаний: 0% ±

Excel 2010

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

ЮMoney 41001841029809

китин

Дата: Четверг, 11.04.2024, 10:44 | Сообщение № 3

Группа: Модераторы

Ранг: Экселист

Сообщений: 7015

Репутация: 1073 ±

Замечаний: 0% ±

Excel 2007;2010;2016

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

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Дата: Четверг, 11.04.2024, 11:49 | Сообщение № 4

Группа: Пользователи

Ранг: Прохожий

Сообщений: 5

Репутация: 0 ±

Замечаний: 0% ±

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

Дата: Четверг, 11.04.2024, 11:51 | Сообщение № 5

Группа: Пользователи

Ранг: Прохожий

Сообщений: 5

Репутация: 0 ±

Замечаний: 0% ±

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

Дата: Четверг, 11.04.2024, 11:59 | Сообщение № 6

Группа: Друзья

Ранг: Экселист

Сообщений: 8761

Репутация: 2273 ±

Замечаний: 0% ±

Excel 2010

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

Цитата Avgust1, 11.04.2024 в 11:51, в сообщении № 5 (

)

Не нашел ссылку на правила.

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

ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Четверг, 11.04.2024, 12:01

Дата: Четверг, 11.04.2024, 13:12 | Сообщение № 7

Группа: Пользователи

Ранг: Прохожий

Сообщений: 5

Репутация: 0 ±

Замечаний: 0% ±

Nic70y, Спасибо за отклик, прилагаю образец файла, код который не получается есть в закомментированном виде в макросе "Перенос_дат_сортировка"

К сообщению приложен файл: fajl_primer.xlsm (144.2 Kb)

Gustav

Дата: Четверг, 11.04.2024, 13:27 | Сообщение № 8

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2748

Репутация: 1137 ±

Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1

Цитата Avgust1, 11.04.2024 в 08:58, в сообщении № 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.]

Цитата mgt, 11.04.2024 в 16:38, в сообщении № 10 (

)

формулалокал нужна

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

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

Сообщение отредактировал Gustav - Четверг, 11.04.2024, 17:57

Дата: Четверг, 11.04.2024, 14:01 | Сообщение № 9

Группа: Друзья

Ранг: Экселист

Сообщений: 8761

Репутация: 2273 ±

Замечаний: 0% ±

Excel 2010

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

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

ЮMoney 41001841029809

mgt

Дата: Четверг, 11.04.2024, 16:38 | Сообщение № 10

Группа: Пользователи

Ранг: Участник

Сообщений: 98

Репутация: 25 ±

Замечаний: 0% ±

Excel 2010

По идее еще формулалокал нужна

Дата: Пятница, 12.04.2024, 08:11 | Сообщение № 11

Группа: Друзья

Ранг: Экселист

Сообщений: 8761

Репутация: 2273 ±

Замечаний: 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

Дата: Суббота, 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)

Дата: Суббота, 13.04.2024, 13:18 | Сообщение № 13

Группа: Друзья

Ранг: Экселист

Сообщений: 8761

Репутация: 2273 ±

Замечаний: 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

Gustav

Дата: Суббота, 13.04.2024, 15:12 | Сообщение № 14

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2748

Репутация: 1137 ±

Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1

Цитата Avgust1, 13.04.2024 в 12:24, в сообщении № 12 (

)

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

Например, так можно:
[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