Цикл вставки строк со сдвигом

Автор Посетитель, 06 октября 2022, 16:44

Посетитель

Добрый день!

У меня задача следующая:
1. На лист "Лист2" в ячейку B12 (она объединенная B12:U12) вводится шифр;
2. Нажимается кнопка "Загр.шифр";
3. Удаляются строки, которые есть между кнопками "Загрузить шифр". Изначально таких строк 2, но почитав ниже вы поймете, что их может быть разное количество;
3. Копируется ячейка Лист2!B12:U12 в Лист5!B12:U12;
4. Если ячейка в диапазоне Лист5!D13:D38 не содержит ошибку, то копируется вся строка из листа "Лист5!" в "Лист2!". Причем не просто копируется а вставляется как новая со сдвигом существующих (в VBA это свойство .insert).

То есть, например, добавили строку Лист5!13:13 после строки Лист2!12:12 (в VBA это свойство .insert). При этом ячейки Лист2!A13:E13, которые мы скопировали из Лист5!A13:E13 будут значениями, а ячейки Лист2!F13:W13 должны быть формулами как ячейки Лист2!G6:W6.
Если так проще, то я могу добавить подобные формулы на лист Лист5!F13:W13 чтобы их в последующем копировать в составе строки.

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

В дополнение:
1. Можно ли записать так, чтобы копировался шифр в следующей справа от кнопки "Загр.шифр" ячейки? Прошу, потому что такой алгоритм должен быть для каждой кнопки на "Лист2!";
2. По аналогии с пунктом выше указанный код записать в относительных ссылках от кнопки "Загр.шифр".

Буду благодарен, за вашу помощь!

Администратор

На листе 2, в ячейке A13 откуда взяты данные?
Я таких данных не вижу на листе 5. Как я понял, нужно скопировать данные из листа 5 на лист 2, но на листе 2, в этой ячейке неизвестные данные.

Посетитель

Откройте скриншот (во вложении).

Мне нужно отображать список секций для 3-х разных вентиляционных установок: UTR, ANR, ANP.
Для UTR будет как на скриншоте под цифрой 1;
Для ANR и ANP как под цифрами 2,3 и т.д.

Для UTR нет шифра установки;
Для ANR и ANP есть.

Его-то я и извлекаю и расписываю: записываю каждую секцию установки. Они отображаются в ячейках Лист5!D13:D38 после того как скопируем шифр и вставим на лист5 (пункт 3).

Теперь то что вы спрашиваете:
для всех установок будет записано так:
Столбец А
Система
[изначально ячейка пуста в каждой строчке; название системы, например П1. Ее введет пользователь после того как запишет шифр, нажмет кнопку и получит список секций в ячейках Лист2!D13:D38]

Столбец B
Тип
[копируется с Листа5]

Столбец С
Т/размер
[копируется с Листа5]

Столбец D
Наименование
[это то самое важное что нужно скопировать как я описал]

Столбец F
Наименование
[тоже нужно скопировать как и D (думал сам расширю код по образцу)]

Столбцы F-W
Столбцы с формулами - я писал о них в письме - нужно как для UTR. Могу добавить на лист5, но помогите сделать чтобы корректно они копировались на лист2 и работали

Администратор

Вы выложили эксель-файл (я его с форума удалил, но он у меня есть).
Ячейка D13 пустая, как её заполнять?

Посетитель

В моем файле на листе5 ячейка D13 и последующие содержат формулу. редактировать содержимое ячеек Лист5!D13:D38 не нужно.
Гляньте еще раз мой алгоритм, который нужно реализовать. Его нужно реализовать исключительно в VBA. Разве что формулы из диапазона Лист2!G6:W6 можете скопировать в Лист5!G13:W38. В алгоритме вам нужен пункт 4 - я в нем и далее все расписал.


У меня задача следующая:
1. На лист "Лист2" в ячейку B12 (она объединенная B12:U12) вводится шифр;
2. Нажимается кнопка "Загр.шифр";
3. Удаляются строки, которые есть между кнопками "Загрузить шифр". Изначально таких строк 2, но почитав ниже вы поймете, что их может быть разное количество;
3. Копируется ячейка Лист2!B12:U12 в Лист5!B12:U12;
4. Если ячейка в диапазоне Лист5!D13:D38 не содержит ошибку, то копируется вся строка из листа "Лист5!" в "Лист2!" (т.е. в Лист2!D13:D38). Причем не просто копируется а вставляется как новая со сдвигом существующих (в VBA это свойство .insert).

То есть, например, добавили строку Лист5!13:13 после строки Лист2!12:12 (в VBA это свойство .insert). При этом ячейки Лист2!A13:E13, которые мы скопировали из Лист5!A13:E13 будут значениями, а ячейки Лист2!F13:W13 должны быть формулами как ячейки Лист2!G6:W6.
Если так проще, то я могу добавить подобные формулы на лист Лист5!F13:W13 чтобы их в последующем копировать в составе строки.

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

В дополнение:
1. Можно ли записать так, чтобы копировался шифр в следующей справа от кнопки "Загр.шифр" ячейки? Прошу, потому что такой алгоритм должен быть для каждой кнопки на "Лист2!";
2. По аналогии с пунктом выше указанный код записать в относительных ссылках от кнопки "Загр.шифр".

P.S. Я снова вложил файл.

Администратор

Файл я взял, он у меня есть. Больше необходимости его выкладывать нет.
В ответе #4 тот же самый текст, что и в первом сообщении?
Я имел ввиду Лист 2, ячейка D13. Как её заполнить. Вы выложили файл, на листе 2, в D13 почему пусто? Вам надо для примера сделать пример, как должно быть.

Посетитель

Теперь я понял, что вы хотите.
Сделал.
Во вложении файл. Лист2! выглядит так как должен после нажатия на кнопку и выполнения всех операций.
Замечу что:
Ячейки Лист2!A13:F13 это значения, а Лист2!G13:W13 - формулы.
В отличие от листа5! строки №14 и №22,23,24,25,26 не были добавлены на лист2!, т.к. в ячейках Лист5!D26 и, соответственно, Лист5!D34:D38 есть ошибка.
Также заполнил столбцы на лист5! чтобы было понятно откуда копируем формулы для Лист2!G13:W13.
Не пугайтесь новых столбцов в диапазоне Лист2!G13:W13 - они раньше были скрыты, а сейчас я просто их показал.




Напомню про дополнение:
"В дополнение:
1. Можно ли записать так, чтобы копировался шифр в следующей справа от кнопки "Загр.шифр" ячейки? Прошу, потому что такой алгоритм должен быть для каждой кнопки на "Лист2!";
2. По аналогии с пунктом выше указанный код записать в относительных ссылках от кнопки "Загр.шифр"."

Администратор

Цитата:
а Лист2!G13:W13 - формулы.

В файле формулы начинаются с H13.

Администратор

Формулы должны остаться такими же, как на листе 5 или измениться?

Администратор

Макрос должен сделать действия, которые делает пользователь:?
1) пользователь переход на лист 2, вставляет пустую строку;
2) пользователь переходит на лист 5, копирует ячейки A13:G13, переходит на лист 2, устанавливает курсор-рамку в столбец A, вставляет значения и форматы без формул;
3) пользователь переходит на лист 5, копирует ячейки H13:W13, переходит на лист 2, устанавливает курсор-рамку в столбец H, вставляет значения, форматы и формулы.

Посетитель

1. Да, вы правы.

        А выпадающий список в ячейках G, K, и т.д. к чему относится? к формулам или условному форматированию?
        Он перенесется с помощью вставки (VBA .insert) или специальной вставки (VBA .PastSpecial)?
       
        Я предполагаю что это все можно реализовать как-то через .row().insert со вставкой как значения (.PastSpecial). Только                                         
        .PastSpecial это простая вставка (.Paste), а не .insert. И я нигде не нашел в интернете как это можно совместить, т.е.
        вставить строки со сдвигом вниз и при этом вставив только значения.

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

        Еще была идея зарезервировать место на Листе5! ниже занимаемых строк: копировать строки (из листа5 на лист5 только
        ниже), вставлять как значения и только потом копировать и вставлять со сдвигом ( .insert) на лист2.

2. По сути такими же ...
Скопируйте их из Лист5!G13:W13 и вставьте Лист2!G13:W13 все же работает?!
Так оно и должно быть

3. Макрос должен сделать действия, которые делает пользователь:?
1) пользователь переход на лист 2, вставляет шифр в ячейку B12;
2) пользователь переходит на лист 5, копирует ячейки A13:G13, переходит на лист 2, устанавливает курсор-рамку в столбец A, вставляет значения и форматы без формул; Он не просто копирует и вставляет - он вставляет со смещением существующих строк на листе 2 внизу, т.е добавляет новую строку.
3) пользователь переходит на лист 5, копирует ячейки H13:W13, переходит на лист 2, устанавливает курсор-рамку в столбец H, вставляет значения, форматы и формулы. Также как описано в предыдущем пункте.

Мне кажется что вы хотите записать макрос. Верно?
По-мойму стандартными способами не обойтись.
Я думал что кто-то напишет цикл по проверке каждой строки, записи ее в массив и только потом вставки со сдвигом.
И при этом это нужно организовать в относительных ссылках (наверное R1C1) чтобы это работала относительно расположения кнопки "загр.шифр"

Администратор

Макрос должен сделать действия, которые делает пользователь:?
1) пользователь на листе 2 удаляет строки между двумя кнопками;
2) пользователь на листе 2 вставляет в B12 шифр;
3) пользователь копирует B12, переходит на лист 5 и вставляет в B12;
4) пользователь переходит на лист 2, вставляет пустую строку, появляется пустая строка 13;
5) пользователь переходит на лист 5, копирует ячейки A13:G13, переходит на лист 2, устанавливает курсор-рамку в A13, вставляет значения и форматы без формул;
6) пользователь переходит на лист 5, копирует ячейки H13:W13, переходит на лист 2, устанавливает курсор-рамку в H13, вставляет значения, форматы и формулы.

Посетитель

Да, только пункты 5 и 6 должны выполняться по следующему условию:
Если ячейка в диапазоне Лист5!D13:D38 не содержит ошибку, то копируется вся строка из листа "Лист5!" в "Лист2!" (т.е. в Лист2!D13:D38).
Если ошибка есть, то выполняются все перечисленные действия для следующей строки.
И в как этот цикл дойдет до последней строки с ошибкой остановится.

Администратор

Макрос не удаляет строки между кнопками и не делает действия относительно кнопки.
Сделан только цикл. По другим вопросам не знаю, смогу ли помочь. Спросите на других форумах.

Предполагается, что перед запуском макроса между кнопками одна пустая строка.
Во время вставки пустой строки, макрос берёт некоторое оформление из этой пустой строки. Т.е. она является шаблоном. Но метод Insert копирует не всё оформление из строки-шаблона.

Макрос
Sub Кнопка2_загрузить_шифр()

    Dim r As Long, i As Long
   
   
    Worksheets("Лист2").Range("B12:U12").Copy Worksheets("Лист5").Range("B12:U12")
   
    ' Курсор r для движения по строкам листа-результата.
    r = 13
    For i = 13 To 38
        If Not IsError(Worksheets("Лист5").Cells(i, "D")) Then
            ' Вставка пустой строки на листе-результате.
                ' Оформление берётся из нижней строки.
            Worksheets("Лист2").Rows(r).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
            ' Копирование значений и форматов без формул.
            Worksheets("Лист5").Rows(i).Columns("A:G").Copy
            Worksheets("Лист2").Cells(r, "A").PasteSpecial xlPasteValues
            Worksheets("Лист2").Cells(r, "A").PasteSpecial xlPasteFormats
            ' Копирование всего, включая формулы.
            Worksheets("Лист5").Rows(i).Columns("H:W").Copy Worksheets("Лист2").Cells(r, "H")
            ' Выход из режима копирования.
            Application.CutCopyMode = False
            ' Перестановка курсора на листе-результате.
            r = r + 1
        End If
    Next i
   
End Sub
[свернуть]

Администратор

Вообще, вы можете создать другие темы и в них задать вопросы.
Просто вам надо будет самим адаптировать полученные ответы в основной макрос.
Например, вы можете задать вопрос: как узнать номер строки, в которой находится кнопка. И тому подобное. То есть небольшие вопросы.