Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Ошибка при повторном запуске макроса: Method 'Insert' of object 'Range' failed error
 
#1
13.11.2021 06:29:32
Добрый день!

Задача макроса: вставлять n-ое кол-во строк (задается Inputbox) перед именным диапазоном.
Проблема: при повторном запуске (редко и в первый раз) выдает ошибку "Method 'Insert' of object 'Range' failed error".
Отмечает строчки (либо-либо):
  1. wsWorkSheet.Range("Прочие").EntireRow.Insert
  2. rngMyCell.Offset(2, 0).EntireRow.Insert
Вопрос: почему так происходит и как устранить.
Код
Sub Файл_1()
Dim wsWorkSheet As Worksheet
Set wsWorkSheet = Workbooks("Файл 1.xlsm").Worksheets("Затраты")

Dim varRow As Variant

varRow = InputBox("Введите сколько строк добавить. Для выхода, ничего не вводите и нажмите ОК", "Добавление нового лота")

    If varRow = "" Then
        MsgBox "Не ввели кол-во строк", vbCritical
        Exit Sub
    ElseIf varRow <= 1 Then
        MsgBox "Слишком мало строк", vbCritical
        Exit Sub
    End If
    
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

wsWorkSheet.Range("Шаблон").Copy                    'Копирование именного диапазона
wsWorkSheet.Range("Прочие").EntireRow.Insert        'Вставка скопированного перед именным диапазоном
Application.CutCopyMode = False

Dim strShablon As String, rngMyCell As Range, i As Long

strShablon = "Шаблон"

Set rngMyCell = wsWorkSheet.Range("Код_КП").Find(strShablon)

    If varRow = 2 Then
        rngMyCell.Offset(1, 0).Rows("1:1").EntireRow.Delete
        
    ElseIf varRow > 2 Then
        For i = 4 To varRow

        Range("Шаблон").Offset(1, 0).Rows("1:1").Copy
        rngMyCell.Offset(2, 0).EntireRow.Insert

        Next
    End If
    
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

MsgBox "Тест завершен!", vbInformation

End Sub

Как вызвать ошибку:
Если добавить например 8 (кол-во любое) строк, потом удалить и снова добавить уже меньше появляется ошибка. Требуется до 8 попыток.

Методы которые не помогли:
  • Изменение переменных
  • Вместо именного диапазона вставка по конкретной строчке, найденной ".Find"
  • Замена "EntireRow.Insert" на "Insert"
  • Указание диапазонов с адресом от книги до листа
Временно нашлось решение после первого добавления и удаления сохранять файл, но случаются "вылеты при первом запуске"

Буду рад любому совет.

Microsoft Office 2016
 
 
 
#2
14.11.2021 13:54:45
Интересный вопрос. У меня на excel 2019 ошибка не вылетает. Добавил в начале 5 строчек - удалил - потом 8 строчек - удалил - потом 4 строчки и все нормально. Прогнал макрос 15 раз на возрастание, потом на уменьшение кол-ва добавляемых строчек и без сохранения. Макрос работал стабильно. Получается, что в более ранних версиях excel какая-то ошибка, если у вас вылетает макрос. Ещё вопросы. Лист/книга защищена паролем? Книга в общем/монопольном доступе?
 
 
 
#3
14.11.2021 15:58:31
Код:
Код
Sub Файл_1()
Dim wsData As Worksheet
Dim varRow As Variant, i

Set wsData = ThisWorkbook.Worksheets("Затраты")
varRow = Application.InputBox("Введите количество строк для добавления и нажмите ОК." & vbCrLf & "Для выхода нажмите отмена", "Добавление нового лота", Type:=1)

    Select Case varRow
        Case Is = 0
            Exit Sub
        Case Is = ""
            MsgBox "Не ввели кол-во строк", vbCritical
            Exit Sub
        Case Is <= 1
            MsgBox "Слишком мало строк", vbCritical
            Exit Sub
    End Select
    
' вставка пустых строк перед Прочие
With wsData
    i = 1
    Do
    .Range("Прочие").Rows(1).EntireRow.Insert shift:=xlDown
    i = i + 1
    Loop While i <= varRow
End With

MsgBox "Тест завершен!", vbInformation
End Sub
Код выше вставляет заданное количество пустых строк перед именованным диапазоном "Прочие"
Игорь Песоцкий, опишите подробнее, что вы хотите получить?
Изменено: artemkau88 - 15.11.2021 08:37:59
 
 
 
#4
15.11.2021 04:57:58
Книга/листы не защищены. Ошибка появляется и в общем и в монопольном доступе.
Изменено: Игорь Песоцкий - 15.11.2021 04:59:02
 
 
 
#5
15.11.2021 05:04:50
Книга/листы не защищены. Ошибка появляется и в общем и в монопольном доступе.

Используя шаблон (последние строчки в таблице) вставить заданное кол-во строк в нужное место.
Сам шаблон имеет два уровня, на первом заголовок, на втором состав лота. Формулы первого и второго уровня отличаются.
 
 
 
#6
15.11.2021 08:36:50
Цитата
написал: шаблон имеет два уровня
То есть мы всегда вставляем последние 2 строки вместе из диапазона Шаблон?

ИЛИ:
Или шаблон имеет 2 уровня значений кроме заголовка
У вас в диапазоне "Шаблон" 3 строки: первая, как я понял, заголовок, вторая и третья - это уровни, которые вставляем ( вставляем либо строку первого уровня, либо второго)?
 
 
 
#7
15.11.2021 08:58:04
Цитата
То есть мы всегда вставляем последние 2 строки диапазона Шаблон?
Верно. Просто на первом уровне формула сумма, поэтому по моей схеме, если оператору необходимо добавить лот с 2 строчками, макрос копирует весь шаблон и удаляет первую строчку второго уровня. Если необходимо вставить больше 3, тогда макрос копирует первую строчку второго уровня шаблона и вставляет ее между ранее вставленными.
Как то запутанно получилось, попробуйте прогнать через отладчик (F8), будет нагляднее.
Цитата
У вас в диапазоне "Шаблон" 3 строки: первая, как я понял, заголовок, вторая и третья - это уровни
В диапазоне шаблон всего два уровня (1 заголовок и сумма, 2 позиция в составе этого лота). В моем примере первого уровня 1 строчка, 2 уровня две строчки. Сделано для того, что бы при добавление новых строк не пришлось перепротягивать формулы
Изменено: Игорь Песоцкий - 15.11.2021 09:03:54
 
 
 
#8
15.11.2021 09:17:45
Если отбросить все специфические особенности макроса, то в сухом остатке проблема в том, что появляется ошибка 1004 «метод Insert из класса Range завершён неверно», я правильно понял? Можете максимально упростить код, чтобы при этом оставалась ошибка?
Изменено: rafsinaga - 15.11.2021 09:18:20
 
 
 
#9
15.11.2021 09:26:07
Игорь Песоцкий, посмотрите код:
Код
Sub Файл_1()
Dim wsData As Worksheet
Dim varRow, RowToPaste As Range, i
Application.CutCopyMode = False
Set wsData = ThisWorkbook.Worksheets("Затраты")
varRow = Application.InputBox("Введите количество строк для добавления и нажмите ОК." & vbCrLf & "Для выхода нажмите отмена", "Добавление нового лота", Type:=1)
With wsData
    Select Case varRow
        Case Is = 0
            Exit Sub
        Case Is = ""
            MsgBox "Не ввели кол-во строк", vbCritical
            Exit Sub
        Case Is <= 1
            MsgBox "Слишком мало строк", vbCritical
            Exit Sub
        Case Is = 2
           Set RowToPaste = .Range("Шаблон").Rows(3)
        Case Is >= 3
           Set RowToPaste = .Range("Шаблон").Rows(2)
    End Select
    
' вставка  строк перед Прочие
    i = 1
    Do
    .Range("Прочие").Rows(1).EntireRow.Insert shift:=xlDown
    RowToPaste.Copy .Range("Прочие").Rows(0)
    i = i + 1
    Loop While i <= varRow
End With
Application.CutCopyMode = False
MsgBox "Тест завершен!", vbInformation
End Sub
Изменено: artemkau88 - 15.11.2021 18:11:56
 
 
 
#10
15.11.2021 09:28:50
Цитата
Если отбросить все специфические особенности макроса, то в сухом остатке проблема в том, что появляется ошибка 1004 «метод Insert из класса Range завершён неверно», я правильно понял?
Правильно. Словно в памяти компьютера остаётся прошлые данные диапазонов (моя теория) и происходит ошибка. И всегда программа отмечает строчку с кодом insert.
 
 
 
#11
15.11.2021 09:36:07
Игорь Песоцкий, код из сообщения 9 не тестировали?
 
 
 
#12
15.11.2021 10:49:48
, последние пол часа этим занимался) Ваш код выдает ошибку '424' object required отмечая строчку 26 (что бы вызвать нужно ввести цифру 3), но это не страшно.
Я правильно уловил, что ошибка описанная мною вызвана конструкцией if...else?  
 
 
 
#13
15.11.2021 11:54:32

Цитата
написал:
выдает ошибку '424' object required отмечая строчку 26

Прошу прощения, не доглядел (исправил код в 9 сообщении) , ошибка была в этой строке:

Код
Case Is > 3

заменил на

Код
Case Is >= 3

Я думаю, что ошибка  здесь, так как вы очищаете буфер после копирования:
Код
wsWorkSheet.Range("Шаблон").Copy                    'Копирование именного диапазона
wsWorkSheet.Range("Прочие").EntireRow.Insert        'Вставка скопированного перед именным диапазоном
Application.CutCopyMode = False

Подправил Ваш код (см вложение)

Изменено: artemkau88 - 15.11.2021 12:10:06
 
 
 
#14
15.11.2021 15:09:45
artemkau88, интегрировал Вашу структуру ("Select Case") в свою задачу, после 20 попыток ошибка не появилась, огромное Вам спасибо!
Ниже рабочий код, вдруг кому пригодится.
Добавил еще один именной диапазон ("СтрочкаШаблона") для копирования
Код
Option Explicit

Sub Тест_1()

Dim wsData As Worksheet
Dim varRow, RowToPaste As Variant, rngMyCell As Range, shablon, i, ColumnToCopy

shablon = "шаблон"

Set wsData = ActiveWorkbook.Worksheets("Затраты")
varRow = Application.InputBox("Введите сколько строк в КП" & vbCrLf, "Добавление нового КП", Type:=1)

    If varRow = "" Then
        MsgBox "Не ввели кол-во строк", vbCritical
        Exit Sub
    ElseIf varRow <= 1 Then
        MsgBox "Слишком мало строк", vbCritical
        Exit Sub
    End If
    
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

With wsData
    Select Case varRow
        Case Is = 2
        Set RowToPaste = .Range("шаблон")
        Set rngMyCell = Range("КодКП").Find(shablon)
        
        RowToPaste.Copy
        .Range("Прочие").Rows.EntireRow.Insert Shift:=xlDown
        
        Set rngMyCell = Range("Код_КП").Find(shablon)
        rngMyCell.Offset(1, 0).Rows(1).EntireRow.Delete Shift:=xlDown
        
        Case Is = 3
        Set RowToPaste = .Range("шаблон")
        RowToPaste.Copy
        .Range("Прочие").Rows.EntireRow.Insert Shift:=xlDown
        
        Case Is > 3
        Set RowToPaste = .Range("шаблон")
        RowToPaste.Copy
        .Range("Прочие").Rows(1).EntireRow.Insert Shift:=xlDown
        
        i = 4
        Set ColumnToCopy = .Range("СтрочкаШаблона").Rows
        Do
        ColumnToCopy.Copy
        Set rngMyCell = Range("Код_КП").Find(shablon)
        rngMyCell.Offset(2, 0).EntireRow.Insert Shift:=xlDown
        i = i + 1
        
        Loop While i <= varRow
     End Select
End With
Application.CutCopyMode = False

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub
Изменено: Игорь Песоцкий - 15.11.2021 15:18:33
 
 
 
#15
15.11.2021 15:35:20
Советы «а можно вот так ещё сделать»
Изменено: Jack Famous - 15.11.2021 15:39:41
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#16
15.11.2021 15:44:21
Jack Famous, спасибо за советы! Возьму на вооружение.
 
 
 
#17
15.11.2021 16:29:38
Цитата
Можете максимально упростить код, чтобы при этом оставалась ошибка?
Не знаю актуально или нет.
Код
Option Explicit

Sub NewKP()
Dim wsData As Worksheet

Set wsData = ThisWorkbook.Worksheets("Затраты")

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

wsData.Range("шаблон").Copy
wsData.Range("Прочие").EntireRow.Insert
Application.CutCopyMode = False

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub
rafsinaga, если закомментировать все Application то ошибка не появляется.
 
 
 
#18
15.11.2021 16:31:20
А разве вам не показывается, на какой строке ошибка? В чём проблема отловить?
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#19
15.11.2021 16:42:43
Ошибку отмечает в 13 строке кода.
Jack Famous имеете в виду использовать"On Error" или что-то другое?
 
 
 
#20
15.11.2021 16:53:31
Цитата
Игорь Песоцкий: Ошибку отмечает в 13 строке кода
ну есессна  :D
Цитата
Игорь Песоцкий: wsData.Range("Прочие").EntireRow.Insert
Я не знаю, что имел ввиду artemkau88, но не лишним будет посмотреть MsgBox wsData.Range("шаблон").Address и MsgBox wsData.Range("Прочие").Address

Скажите словами, что и куда копируете
Изменено: Jack Famous - 15.11.2021 17:00:26
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#21
15.11.2021 16:56:59
Копирую именной диапазон (шаблон) и вставляю перед именным диапазоном (Прочие)
 
 
 
#22
15.11.2021 17:02:06
Игорь Песоцкий, гляньте размеры диапазонов, как я показал
Вставить перед (это выше или куда) БЕЗ вставки строк для новых данных - это значит ЗАМЕНИТЬ старые данными новыми
Чтобы вставить выше, надо вставить пустые строки, чтоб хватило и уже в ПОДГОТОВЛЕННОЕ место вставлять новые данные

UPD: Нашёл файл и сам гляну
UPD2: по задаче вам вообще просто ПУСТЫЕ строки вставлять надо - смотрю дальше  :D
Изменено: Jack Famous - 15.11.2021 17:07:28
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#23
15.11.2021 17:14:59
Давайте с самого начала
Этот код вставит 5 строк НАД выделением
что не устравивает?  :)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#24
15.11.2021 17:15:05
Вставка происходит со сдвигом "Прочих" вниз. Т.е. прочие начинались с 24 строки после insert начинаются с 27.
В данный момент ошибка появилась на 3 раз.

Диапазоны показывает перед тем как выдать ошибку. Если не сложно поясните, как использовать эту информацию.
Код
wsData.Range("шаблон").Copy
MsgBox wsData.Range("шаблон").Address
MsgBox wsData.Range("Прочие").Address
wsData.Range("Прочие").EntireRow.Insert
Application.CutCopyMode = False
 
 
 
#25
15.11.2021 17:19:11
Игорь Песоцкий, см. сообщение выше (#23) и отвечайте)
Изменено: Jack Famous - 15.11.2021 17:19:31
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#26
15.11.2021 17:27:21
Цитата
что не устравивает?
Цель макроса была несколько сложнее.
Скопировать шаблон состоящий из 3 строчек, который в свою очередь состоял из 2 уровней (1-й - заголовок и формулы суммирования и 2-й уровень- 2 строчки с данными)).
Вставить шаблон со сдвигом в заданном месте и вставить заданное кол-во строк.
Пользователь макроса просто вводит сколько строк в лоте (20, 30 не важно) и все.
Недостающие строчки встают между 1 и 2 строчкой второго уровня (что бы формулы суммирования не сломалась).
Далее макрос заполняет формулы (я этот участок не выкладывал).

Изначально прописал сценарий через "If...Else", но появлялась ошибка через раз. Пользователь  artemkau88 предложил конструкцию "Select case".
Внедрил ее к себе, прогнал код около 40 раз и ошибка не появилась.
Все прекрасно только я не понял почему конструкция если приводила к ошибке или в чем дело.
 
 
 
#27
15.11.2021 17:37:48
Нашёл возможность прогнать макрос на версии excel 2016, действительно, вылетает ошибка 1004 "метод Insert объект Range завершен неверно". Подсвечивается строчка 22 изначального кода. Что интересно, решение которое предложил , работает без сбоя.

, по существу ошибки и о причинах сказать ничего не могу, но помню, что в моих кодах она также возникала время от времени. Приходилось обходиться различными костылями. Возможно, каким-то образом, засоряется кэш (?), память (?). Хотя два раза ошибка выскочила при первом запуске кода, указанного в сообщении #1, но чаще на 2 и следующий разы.  
 
 
 
#28
15.11.2021 17:52:59
Игорь Песоцкий, ну раз устраивает, то пользуйтесь - не будем ворошить то, что работает  :D
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#29
15.11.2021 18:13:53
del
Изменено: artemkau88 - 16.11.2021 12:05:46
 
 
 
Страницы: 1
Наверх