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

@jony_kosht · Регистрация: 21.08.2021

Author24 — интернет-сервис помощи студентам

Добрый день!

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

38 не содержит ошибку, то копируется вся строка из листа "Лист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. По аналогии с пунктом выше указанный код записать в относительных ссылках от кнопки "Загр.шифр".

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

@АЕ · 06.10.2022, 20:32

jony_kosht, надеюсь так произойдет и с этим вопросом.

Сообщение от jony_kosht

Всем спасибо, извините за беспокойство!
Я отвлекся, выспался и нашел другой подход к решению задачи (с помощью обычных формул).
Если надо могу скинуть решение.

@jony_kosht · 06.10.2022, 23:07 **[ТС]**

Как-то не уважительно Вы ...

Мне нужна была помощь в той теме, но ответ не был найден, я решил вопрос с помощью костылей.

Если не хотите или не можете помочь, то так и напишите.

@АЕ · 06.10.2022, 23:27

Сообщение от jony_kosht

Если не хотите или не можете помочь, то так и напишите.

Хорошо, так и напишу. Из двух или. Не хочу.

@Dinoxromniy · 07.10.2022, 11:07

Сообщение от jony_kosht

Если не хотите или не можете помочь, то так и напишите.

Колхоз - дело добровольное, форум - тем более.

Сообщение от jony_kosht

Если так проще, то я могу добавить подобные формулы

Проще, если вы уберете с листа 2 все кнопки, а вместо них в необходимые ячейки вобъете "Загр.шифр".
Тогда код можно запускать по двойному щелчку по ячейке, и соответственно отступать от них необходимое количество.

Сообщение от jony_kosht

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

Мало что понял.
Пример кода для варианта, описанного выше (кнопки - убрать, вписать текст на их место). Допилите под ваши нужды (что там куда должно копироваться), или опишите более понятно, что он должен делать.

Visual Basic

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 
Cancel = True
If Target.Value = "Загр.шифр" Then
Target.Resize(1, 21).Copy Worksheets("Лист5").Cells(Target.Row, 2)
    
     i = Target.Row + 1
     Do While (Not Application.WorksheetFunction.IsError(Worksheets("Лист5").Cells(i, 4).Value)) And Worksheets("Лист2").Cells(i, 1).Value <> "Загр.шифр" And Not IsEmpty(Worksheets("Лист2").Cells(i, 1).Value)
        
            Worksheets("Лист5").Cells(i, 4).Copy
            Worksheets("Лист2").Cells(i, 4).Insert Shift:=xlShiftDown
            i = i + 1
            
        
   Loop
End If
End Sub

@jony_kosht · 07.10.2022, 11:12 **[ТС]**

АЕ, мне одного не понятно:
Чем же я вам насолил?
Или может задел?

Прежде чем написать на форум и попросить помощи я попробовал найти решение самостоятельно пересмотрев 30-40 сайтов и проискав в книге по самоучителю VBA в течении 2-х дней. И только потом обратился на форум за помощью.

Надеюсь вам не придется искать ответы как мне и в вашем окружении всегда найдутся люди, которые вам помогут. Удачи и успехов Вам!

@jony_kosht · 07.10.2022, 11:21 **[ТС]**

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

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

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

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

38
после того как скопируем шифр и вставим на лист5 (пункт 3).

Как можно заметить я хочу совместить UTR и шифр ANR и ANP.

2. Заменил кнопки на текст.

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

38 есть ошибка.
Также заполнил столбцы на лист5! чтобы было понятно откуда копируем формулы для Лист2!G13:W13.
Не пугайтесь новых столбцов в диапазоне Лист2!G13:W13 - они раньше были скрыты, а сейчас я просто их показал.

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

@Dinoxromniy · 07.10.2022, 11:25

Не по теме:

Сообщение от jony_kosht

Чем же я вам насолил?
Или может задел?

Как любит говорить мой знакомый охотник про свои охотничьи трофеи: "И ведь эта птичка ничего плохого мне не сделала! Представляете, что я делаю со своими врагами?!" (с)

@jony_kosht · 07.10.2022, 11:26 **[ТС]**

Я предполагаю что это все можно реализовать как-то через .row().insert со вставкой как значения (.PastSpecial). Только
.PastSpecial это простая вставка (.Paste), а не .insert. И я нигде не нашел в интернете как это можно совместить, т.е.
вставить строки со сдвигом вниз и при этом вставив только значения.

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

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

@Dinoxromniy · 07.10.2022, 11:36

Сообщение от jony_kosht

И я нигде не нашел в интернете как это можно совместить, т.е.
вставить строки со сдвигом вниз и при этом вставив только значения.

Вставить пустую строку. Затем - Cells(14,2).value=Cells(14,1).value. Ну и запись макрорекордера никто не отменял, запишите и посмотрите, как это на VBA будет выглядеть.

@jony_kosht · 07.10.2022, 12:08 **[ТС]**

Понятно, спасибо!

Т.к. у меня с циклами и использованием переменной в них не так много знаний, то прошу помочь с кодом.

Или я могу забирать тот, что выше?

Кстати, можете посоветовать хорошую книжку или видео про VBA?

Добавлено через 21 минуту
Dinoxromniy,

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

В вашем коде ДаблКлик должен быть по "кнопке" (ячейке) "Загр.шифр" и только для нее снизу добавятся строки? Остальные уже загруженные ячейки сверху или снизу (со своими кнопками "Загр.шифр") не изменятся?!

@Dinoxromniy · 07.10.2022, 12:18

Сообщение от jony_kosht

или видео про VBA?

Не уверен, что оно того стоит, хотя и не пробовал.

Сообщение от jony_kosht

хорошую книжку

Кузьменко В.Г. - VBA, более чем полно.

Сообщение от jony_kosht

Или я могу забирать тот, что выше?

Ну я подозреваю, что делает оно не совсем то, что нужно, но времени врубаться в это сейчас к сожалению нет.

Сообщение от jony_kosht

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

Ну вот если кнопку убрать, и заменить на ячейку, по которой просто нужно дважды щелкнуть, как по кнопке - задача резко упрощается.

@jony_kosht · 07.10.2022, 13:39 **[ТС]**

В общем понятно.
Спасибо вам большое за код!!!
Буду сидеть допиливать его под мои нужды. Скелет точно есть, только нужно расставить верные ячейки и строки.
Я хотел цикл с условием и это плюс-минус то что нужно.

Если на чем-то споткнусь, то отпишусь в этой теме.

Добавлено через 57 минут
Подскажите, что тут не верно записано?

Visual Basic

1	Worksheets("Лист5").Range(Cells(b, 1), Cells(b, 6)).Value = Worksheets("Лист2").Cells(i, 1).Value

Вот сам код:

Visual Basic

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 
Cancel = True
If Target.Value = "Загр.шифр" Then
Target.Offset(0, 1).Resize(1, 20).Copy Worksheets("Лист5").Cells(Target.Row, 2)
    
     i = Target.Row + 1
     b = 13
     Do While Worksheets("Лист2").Cells(i, 1).Value <> "Загр.шифр" And Not IsEmpty(Worksheets("Лист5").Cells(b, 4).Value)
        If Not Application.WorksheetFunction.IsError(Worksheets("Лист5").Cells(b, 4).Value) Then
            Worksheets("Лист2").Rows(i).Insert Shift:=xlShiftDown
            MsgBox i
            MsgBox b
            Worksheets("Лист5").Range(Cells(b, 1), Cells(b, 6)).Value = Worksheets("Лист2").Cells(i, 1).Value
            i = i + 1
        End If
        b = b + 1
            
        
   Loop
End If
End Sub

@Punkt5 · 07.10.2022, 14:09

Сообщение от jony_kosht

что тут не верно записано?
Worksheets("Лист5").Range(Cells(b, 1), Cells(b, 6)).Value = Worksheets("Лист2").Cells(i, 1).Value

Как понимаю, вы хотите во все 6 ячеек строки b, вычисляемой на листе "Лист5" записать одно и то же значение из ячейки (i,1), что вычисляется на листе "Лист2"?
Если ваше желание именно таково, то стоит помнить, что не только .Range(), но и .Cells(), которые вы вставляете внутрь этого .Range() необходимо описывать с полным префиксом, если есть вероятность, что в этот момент требуемый лист не является активным. то есть, надо:

Visual Basic

1	Worksheets("Лист5").Range(Worksheets("Лист5").Cells(b, 1), Worksheets("Лист5").Cells(b, 6)).Value = Worksheets("Лист2").Cells(i, 1).Value

Если же вы хотели скопировать 6 ячеек с "Лист2" в 6 ячеек "Лист5", то лучше попробоват так:

Visual Basic

Worksheets("Лист5").Range(Worksheets("Лист5").Cells(b, 1), Worksheets("Лист5").Cells(b, 6)).Value = Worksheets("Лист2").Range(Worksheets("Лист2").Cells(i, 1),Worksheets("Лист2").Cells(i, 6)).Value

Если же у вас при этом могут быть открыты более одного файла, то перед Worksheet везде желательно однозначно указывать соответствующий WorkBook.

@jony_kosht · 07.10.2022, 17:32 **[ТС]**

Punkt5,

Я имел ввиду второе.

Спасибо вам большое!!!

Добавлено через 3 часа 15 минут
Напишу снова в этой теме, т.к. похожий вопрос.

Можно ли добавить второе действие к событию BeforeDoubleClick ???

В приведенном примере мне помогли реализовать событие двойного щелчка по ячейке с текстом "Загр.шифр". Можно ли добавить еще одну такую ячейку с другим текстом, например, "Очистить" ?
1.После нажатия на нее нужно чтобы проверялась каждая ячейка в столбце 1 на содержимое "Загр.шифр";
2. Если находим, то проверяем содержимое следующей строки. Если оно не пусто, то удаляем всю строку со смещением вверх;
3. Так с каждой строкой пока либо не найдем пустую строку.
4. Если находим пустую строку, проверяем следующие ячейки первого столбца на с содержимое "Загр.шифр".
5. Если в пределах 100 ячеек первого столбца кнопки нет, то останавливаем цикл.

Я попробовал реализовать, но ничего не происходит по двойному клику по кнопке "Очистить".
Помогите пожалуйста!

Visual Basic

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 
Cancel = True
If Target.Value = "Загр.шифр" Then
...
End If
 
If Target.Value = "Очистить" Then
    с = Target.Row + 1
    Do While Worksheets("Лист2").Cells(с, 1).Value = "Загр.шифр"
        
        Do While Not IsEmpty(Worksheets("Лист2").Range(Worksheets("Лист2").Cells(с + 1, 1), Worksheets("Лист2").Cells(с + 1, 30)).Value)
           Worksheets("Лист2").Rows(с + 1).Delete xlShiftUp
        Loop
        
    c = c + 1
    Loop
End If

@Dinoxromniy · 07.10.2022, 17:39

Сообщение от jony_kosht

Можно ли добавить второе действие к событию BeforeDoubleClick ???

Можно любое количество.
jony_kosht, оформляйте код через кнопку (выше окна, в которое вы вбиваете ответ, слева ряд кнопок языков - php, HTML, JS, JAVA, VB. Так вот вам нужна VB).

У вас достаточно сложные условия - нужно чтобы текущая кнопка была с "Очистить", а ниже нее "Загр.шифр", а потом еще вот это вот условие -

Not IsEmpty(Worksheets("Лист2").Range(Worksheets("Лист 2").Cells(с + 1, 1), Worksheets("Лист2").Cells(с + 1, 30)).Value)

Видимо, одно из них не выполняется, поэтому строчка не работает.

@jony_kosht · 07.10.2022, 18:08 **[ТС]**

порядок записи в процедуре имеет значение?

Visual Basic

If Target.Value = "Загр.шифр" Then
...
End If
 
If Target.Value = "Очистить" Then
...
End If

Тут я пытался записать этот пункт:
2. Если находим, то проверяем содержимое следующей строки. Если оно не пусто, то удаляем всю строку со смещением вверх;
Тут я проверяю всего лишь 30 ячеек. И потом удаляю строку.

Так первый оператор End if же работает:

Visual Basic

1
2
3

If Target.Value = "Загр.шифр" Then
...
End If

@Dinoxromniy · 07.10.2022, 18:09

Сообщение от jony_kosht

порядок записи в процедуре имеет значение?

В этом контексте - нет, не имеет.
И что покажет If Target.Value = "Загр.шифр" Then msgbox "Done" ?

@jony_kosht · 07.10.2022, 18:12 **[ТС]**

А как цитату вставлять?

Это куда нужно вставить в коде?
И что покажет

Visual Basic

1	If Target.Value = "Загр.шифр" Then msgbox "Done"

?

@Dinoxromniy · 07.10.2022, 18:15

Сообщение от jony_kosht

А как цитату вставлять?

Если в браузере нет запрета на выполнение скриптов - выделите любой текст, рядом с ним всплывет кнопка "цитировать".

Сообщение от jony_kosht

Это куда нужно вставить в коде?

Ну вы же можете отладку начать с самого простого: пусть ваша процедура изначально будет такая, а потом уже понавешаете на нее добавок:

Visual Basic

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Value = "Загр.шифр" Then msgbox "Загр.шифр"
If Target.Value = "Очистить" Then msgbox "Очистить"
end sub

@Dinoxromniy Динохромный 1375 / 749 / 271 Регистрация: 22.12.2015 Сообщений: 2,341
	07.10.2022, 17:39	16
	Сообщение от jony_kosht Можно ли добавить второе действие к событию BeforeDoubleClick ??? Можно любое количество. jony_kosht, оформляйте код через кнопку (выше окна, в которое вы вбиваете ответ, слева ряд кнопок языков - php, HTML, JS, JAVA, VB. Так вот вам нужна VB). У вас достаточно сложные условия - нужно чтобы текущая кнопка была с "Очистить", а ниже нее "Загр.шифр", а потом еще вот это вот условие - `Not IsEmpty(Worksheets("Лист2").Range(Worksheets("Лист 2").Cells(с + 1, 1), Worksheets("Лист2").Cells(с + 1, 30)).Value)` Видимо, одно из них не выполняется, поэтому строчка не работает. 1

@АЕ ᴁ® 3648 / 2004 / 427 Регистрация: 13.12.2016 Сообщений: 6,894 Записей в блоге: 5
	06.10.2022, 20:32	2
	jony_kosht, надеюсь так произойдет и с этим вопросом. Сообщение от jony_kosht Всем спасибо, извините за беспокойство! Я отвлекся, выспался и нашел другой подход к решению задачи (с помощью обычных формул). Если надо могу скинуть решение. 0

@jony_kosht 0 / 0 / 0 Регистрация: 21.08.2021 Сообщений: 23
	06.10.2022, 23:07 [ТС]	3
	Как-то не уважительно Вы ... Мне нужна была помощь в той теме, но ответ не был найден, я решил вопрос с помощью костылей. Если не хотите или не можете помочь, то так и напишите. 0

@АЕ ᴁ® 3648 / 2004 / 427 Регистрация: 13.12.2016 Сообщений: 6,894 Записей в блоге: 5
	06.10.2022, 23:27	4
	Сообщение от jony_kosht Если не хотите или не можете помочь, то так и напишите. Хорошо, так и напишу. Из двух или. Не хочу. 0

@jony_kosht 0 / 0 / 0 Регистрация: 21.08.2021 Сообщений: 23
	07.10.2022, 11:12 [ТС]	6
	АЕ, мне одного не понятно: Чем же я вам насолил? Или может задел? Прежде чем написать на форум и попросить помощи я попробовал найти решение самостоятельно пересмотрев 30-40 сайтов и проискав в книге по самоучителю VBA в течении 2-х дней. И только потом обратился на форум за помощью. Надеюсь вам не придется искать ответы как мне и в вашем окружении всегда найдутся люди, которые вам помогут. Удачи и успехов Вам! 0

@Dinoxromniy Динохромный 1375 / 749 / 271 Регистрация: 22.12.2015 Сообщений: 2,341
	07.10.2022, 11:25	8
	Не по теме: Сообщение от jony_kosht Чем же я вам насолил? Или может задел? Как любит говорить мой знакомый охотник про свои охотничьи трофеи: "И ведь эта птичка ничего плохого мне не сделала! Представляете, что я делаю со своими врагами?!" (с) 0

@jony_kosht 0 / 0 / 0 Регистрация: 21.08.2021 Сообщений: 23
	07.10.2022, 11:26 [ТС]	9
	Я предполагаю что это все можно реализовать как-то через .row().insert со вставкой как значения (.PastSpecial). Только .PastSpecial это простая вставка (.Paste), а не .insert. И я нигде не нашел в интернете как это можно совместить, т.е. вставить строки со сдвигом вниз и при этом вставив только значения. Только потом я понял что часть ячеек будет с формулами и у меня голова сразу взорвалась. Еще была идея зарезервировать место на Листе5! ниже занимаемых строк: копировать строки (из листа5 на лист5 только ниже), вставлять как значения и только потом копировать и вставлять со сдвигом ( .insert) на лист2. 0

@Dinoxromniy Динохромный 1375 / 749 / 271 Регистрация: 22.12.2015 Сообщений: 2,341
	07.10.2022, 11:36	10
	Сообщение от jony_kosht И я нигде не нашел в интернете как это можно совместить, т.е. вставить строки со сдвигом вниз и при этом вставив только значения. Вставить пустую строку. Затем - Cells(14,2).value=Cells(14,1).value. Ну и запись макрорекордера никто не отменял, запишите и посмотрите, как это на VBA будет выглядеть. 0

@jony_kosht 0 / 0 / 0 Регистрация: 21.08.2021 Сообщений: 23
	07.10.2022, 12:08 [ТС]	11
	Понятно, спасибо! Т.к. у меня с циклами и использованием переменной в них не так много знаний, то прошу помочь с кодом. Или я могу забирать тот, что выше? Кстати, можете посоветовать хорошую книжку или видео про VBA? Добавлено через 21 минуту Dinoxromniy, На лист могут быть добавлены несколько раз шифры установок. Следовательно нужно, чтобы код выполнялся: 1. несколько раз; 2. Выполнялся только рядом с той кнопкой "Загр.шифр" рядом с которой вставлен шифр. В вашем коде ДаблКлик должен быть по "кнопке" (ячейке) "Загр.шифр" и только для нее снизу добавятся строки? Остальные уже загруженные ячейки сверху или снизу (со своими кнопками "Загр.шифр") не изменятся?! 0

@Dinoxromniy Динохромный 1375 / 749 / 271 Регистрация: 22.12.2015 Сообщений: 2,341
	07.10.2022, 18:09	18
	Сообщение от jony_kosht порядок записи в процедуре имеет значение? В этом контексте - нет, не имеет. И что покажет `If Target.Value = "Загр.шифр" Then msgbox "Done"` ? 1