Макрос расчета по формуле при переходе по строкам.

[Masha]

Всем привет. Я тут на форуме впервые. Знаний по VBA практически ноль, т.ч. прошу отнестись снисходительно. Вопрос у меня такой. Есть таблица в диапазоне В3:G11, в приложенном файле при нажатии в столбце А в строках 3-11 справа от таблицы происходит расчет данных , выбранной строки. Всё работает нормально. Теперь хочу, чтобы всё работало также, но от макроса при нажатии сочетания клавиш Ctrl+m. Макрос я пробовала написать, но толком ничего не получилось. Всё что вышло так вот это

Sub Макрос2()

    Worksheets("Лист1").Activate
 
    Dim lr&
    lr = Columns.Row
    Application.ScreenUpdating = False
    Range("I3").Select
    ActiveCell.Formula2R1C1 = _
        "=--SUBSTITUTE(DATE(ROW(INDIRECT(YEAR(R" & lr & "C[-7])&"":""&YEAR(R" & lr & "C[-6]))),1,1),SMALL(DATE(ROW(INDIRECT(YEAR(R" & lr & "C[-7])&"":""&YEAR(R" & lr & "C[-6]))),1,1),1),R" & lr & "C[-7])"

   Range("N3").Select
 
End Sub

Макрос вроде считает по формуле правильно, но только если данные находятся в первой строке, а надо чтобы расчет начинался с 3-ей строки и при выборе какой-либо другой строки, происходил расчет данных из выбранной строки. Мой макрос в модуле2. Файлы я прикрепила. Всем спасибо.

 

[ChatGPT]

@AssistGPT

Как решить вопросы?

Привет! Я могу помочь тебе с написанием макроса, который будет выполнять расчет данных из выбранной строки при нажатии на сочетание клавиш Ctrl+m.

Попробуйте использовать следующий код:

Sub CalculateRow()
    Dim selectedRow As Integer
    selectedRow = ActiveCell.Row
    
    Range("I" & selectedRow).FormulaR1C1 = "=--SUBSTITUTE(DATE(ROW(INDIRECT(YEAR(R" & selectedRow & "C[-7])&"":""&YEAR(R" & selectedRow & "C[-6]))),1,1),SMALL(DATE(ROW(INDIRECT(YEAR(R" & selectedRow & "C[-7])&"":""&YEAR(R" & selectedRow & "C[-6]))),1,1),1),R" & selectedRow & "C[-7])"
End Sub

Этот макрос будет брать номер выбранной строки и выполнять расчет данных из этой строки в столбце I.

Чтобы назначить этот макрос на сочетание клавиш Ctrl+m, выполните следующие действия:

1. Нажмите Alt+F8, чтобы открыть диалоговое окно "Макросы".
2. Выберите макрос "CalculateRow" и нажмите кнопку "Параметры".
3. Введите "m" в поле "Буква" и нажмите Ctrl в поле "Клавиша".
4. Нажмите "OK" и закройте диалоговое окно "Макросы".

Теперь, когда вы выбираете строку в таблице и нажимаете Ctrl+m, макрос будет выполнять расчет данных из выбранной строки в столбце I.

Надеюсь, это поможет! Если у вас есть какие-либо вопросы, не стесняйтесь спрашивать.

 

[Masha]

Спасибо за быстрый от вет. Макрос срабатывает, но результат выдает не в том виде как хотелось бы и всё потому, что в формуле появляется значок @ и результат вычисления пишется в одну строку, а надо чтобы выводился весь массив расчета. Если убрать из формулы значок @, будет работать нормально, как на картинке что я прислала.
=--ПОДСТАВИТЬ(ДАТА(@СТРОКА(ДВССЫЛ(ГОД(B$4)&":"&ГОД(C$4)));1;1);НАИМЕНЬШИЙ(ДАТА(@СТРОКА(ДВССЫЛ(ГОД(B$4)&":"&ГОД(C$4)));1;1);1);B$4)
Откройте мой файл и одинарный клик а в столбе А в строках 3:11, вот такой результат мне надо. В файле у меня шесть формул, в примере я представила только одну, думаю, что этого вполне достаточно. И ещё одна проблема в том, что результат выводится в той же строке, где выделена ячейка, а не начиная со строки 3.

 

[Masha]

Вот картинка результата расчета столбца В строки 3. Вот так надо, чтобы отображался расчет выбранной строки. При выполнении Вашего макроса получается картинка Макрос_3. Обратите внимание на разницу формул в строке формул.

 

[Masha]

Одну проблему я нашла и исправила в Вашем макросе вместо FormulaR1C1 пишу Formula2R1C1 и расчет, так скажем, выводится развернутым. Осталось сделать так чтобы результаты расчетов выводились не напротив выбранной строки ,а с 3-ей строки столбца I.

 

[Dragokas]

Добрый вечер @Masha и добро пожаловать на SafeZone!

Если еще актуально, сейчас установлю MS Office 2021 (т.к. у вас используются новые фичи этой версии) и попробуем разобраться.

 

[Masha]

Добрый вечер @Masha и добро пожаловать на SafeZone!

Если еще актуально, сейчас установлю MS Office 2021 (т.к. у вас используются новые фичи этой версии) и попробуем разобраться.

Спасибо при использовании FormulaR1C1 и Formula2R1C1 всё работает нормально. Лучше бы с макросам кто-нибудь помог.

 

[Dragokas]

Осталось сделать так чтобы результаты расчетов выводились не напротив выбранной строки ,а с 3-ей строки столбца I.

С этим помощь ещё нужна?

 

[Masha]

С этим помощь ещё нужна?

Да.

 

[Dragokas]

Похоже, я не понимаю вопроса.
Давайте по-порядку, такой макрос вы запускаете? (или похожий на него)

Sub Макрос2()

    Application.ScreenUpdating = False

    Dim lr&
    lr = ActiveCell.Row
    
    Range("I3").Formula2R1C1 = _
        "=--SUBSTITUTE(DATE(ROW(INDIRECT(YEAR(R" & lr & "C[-7])&"":""&YEAR(R" & lr & "C[-6]))),1,1),SMALL(DATE(ROW(INDIRECT(YEAR(R" & lr & "C[-7])&"":""&YEAR(R" & lr & "C[-6]))),1,1),1),R" & lr & "C[-7])"
    
    Application.ScreenUpdating = True
 
End Sub

Нажимаю на любую строку, где есть данные, затем Ctrl + M, и результаты попадают массивом, начиная с ячейки I3, как вы и просили.

 

[Dragokas]

А... вам нужно тоже самое, что в Worksheet_SelectionChange, только на комбинацию клавиш.
Для этого повторять код не обязательно.
Можно просто перенести его в модуль и сделать публичным.
Посмотрите, как сделал в прикреплённом файле.

 

[Masha]

Похоже, я не понимаю вопроса.
Давайте по-порядку, такой макрос вы запускаете? (или похожий на него)

Sub Макрос2()

    Application.ScreenUpdating = False

    Dim lr&
    lr = ActiveCell.Row
  
    Range("I3").Formula2R1C1 = _
        "=--SUBSTITUTE(DATE(ROW(INDIRECT(YEAR(R" & lr & "C[-7])&"":""&YEAR(R" & lr & "C[-6]))),1,1),SMALL(DATE(ROW(INDIRECT(YEAR(R" & lr & "C[-7])&"":""&YEAR(R" & lr & "C[-6]))),1,1),1),R" & lr & "C[-7])"
  
    Application.ScreenUpdating = True
 
End Sub

Нажимаю на любую строку, где есть данные, затем Ctrl + M, и результаты попадают массивом, начиная с ячейки I3, как вы и просили.

Нет, ранее мне был предложен другой макрос

Sub CalculateRow() Dim selectedRow As Integer selectedRow = ActiveCell.Row Range("I" & selectedRow).Formula2R1C1 = "=--SUBSTITUTE(DATE(ROW(INDIRECT(YEAR(R" & selectedRow & "C[-7])&"":""&YEAR(R" & selectedRow & "C[-6]))),1,1),SMALL(DATE(ROW(INDIRECT(YEAR(R" & selectedRow & "C[-7])&"":""&YEAR(R" & selectedRow & "C[-6]))),1,1),1),R" & selectedRow & "C[-7])" Range("N3").Select End Sub

он работал не так как надо. Работу вашего кода проверяю. Проверила, спасибо работает.

 

[Dragokas]

Или воспользоваться таким "хаком":

Sub Макрос2()

    Dim saveCell As Range
    Set saveCell = ActiveCell
    Cells(ActiveCell.Row, "A").Select
    saveCell.Select

End Sub

Сыммитирует нажатие в колонке A, чем спровоцирует вызов макроса, который назначен на это нажатие.

PS. Вообще, адские спагетти-формулы, никогда не понимал, как люди умеют читать/писать такие длинные строчки, ничего не напутав.

 

[Dragokas]

Нет, ранее мне был предложен другой макрос

и вопрос не по теме, как вам удаётся вставлять код в таком виде?

[CODE title="Sub CalculateRow() Dim selectedRow As Integer selectedRow = ActiveCell.Row Range("I" & selectedRow).Formula2R1C1 = "=--SUBSTITUTE(DATE(ROW(INDIRECT(YEAR(R" & selectedRow & "C[-7])&"":""&YEAR(R" & selectedRow & "C[-6]))),1,1),SMALL(DATE(ROW(INDIRECT(YEAR(R" & selectedRow & "C[-7])&"":""&YEAR(R" & selectedRow & "C[-6]))),1,1),1),R" & selectedRow & "C[-7])" Range("N3").Select End Sub"][/CODE]

Вот здесь есть инструкция в картинках, как правильно и быстро вставлять код на форум:

Как оформить тему в разделе VBA

Макросы Word, Excel (VBA) Этот раздел посвящен макросам офисных документов Microsoft Office (VBA). Как оформить тему Обрамление кода (теги) - Нажимаем кнопку "Вставить" => "Код": - Добавляем код, выбираем из списка язык "VB.NET / VBA": либо просто печатаем Ваш код

www.safezone.cc

 

[Masha]

А... вам нужно тоже самое, что в Worksheet_SelectionChange, только на комбинацию клавиш.
Для этого повторять код не обязательно.
Можно просто перенести его в модуль и сделать публичным.
Посмотрите, как сделал в прикреплённом файле.

Ещё раз спасибо. Всё работает как надо.

 

[Dragokas]

Пожалуйста, обращайтесь.
Удачи!

 

[Masha]

и вопрос не по теме, как вам удаётся вставлять код в таком виде?

Вот здесь есть инструкция в картинках, как правильно и быстро вставлять код на форум:

Как оформить тему в разделе VBA

Макросы Word, Excel (VBA) Этот раздел посвящен макросам офисных документов Microsoft Office (VBA). Как оформить тему Обрамление кода (теги) - Нажимаем кнопку "Вставить" => "Код": - Добавляем код, выбираем из списка язык "VB.NET / VBA": либо просто печатаем Ваш код

www.safezone.cc

Я тут впервые ещё плохо тут ориентируюсь. Позже разберусь. Благодарю за помощь.

 

[Dragokas]

Я тут впервые ещё плохо тут ориентируюсь. Позже разберусь. Благодарю за помощь.

Это больше не как замечание, а вопрос (чтобы другие пользователи не столкнулись с тем же).
Каким образом у вас получились такие теги. Вы написали от руки или как-то иначе?
Лично у меня не получилось воспроизвести тоже самое.

 

[akok]

Лично у меня не получилось воспроизвести тоже самое.

просто код вставлен не в то поле

 

[Dragokas]

@akok, ох, такое простое меню наусложняли ...