Excel VBA для запуска макроса в новой строке добавлен
Я пытаюсь, чтобы моя таблица автоматически принимала формат и формулы предыдущих строк при вставке новой строки.
Я прочитал, где вы можете настроить свой листок, чтобы автоматически запускать код, если внесено изменение, но мне трудно заставить код работать.
Я пробовал следующее, и каждый раз, когда я вставляю новую строку, он продолжает добавлять строку до тех пор, пока она не получит ошибку, и мне нужно заставить выйти:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Range("A1:D25") = ActiveCell.EntireRow.Insert Then Cells(1, 2).Value = 10 End If End Sub
Я добавил значение Cell Value = 10
чтобы увидеть, будет ли оно работать. Это был просто тест, но он все еще терпит неудачу.
Кто-нибудь знает о возможном решении?
В вашем коде есть две основные проблемы:
- Вы вызываете каскад событий. Т.е. ваше событие изменения инициирует дальнейшие изменения событий
-
.Insert
не делает то, что вы, кажется, думаете, что это так. Он не обнаруживает вставленные строки, он вставляет строки.
Я предполагаю, что «… вставить новую строку …» означает « Вставить целую строку»
Эта демонстрация позволяет избежать каскада с помощью .EnableEvents = False
и использует Copy
, pasteSpecial
для копирования форматов и формул.
Option Explicit Dim RowsCount As Long ' Variable to track number of rows used in sheet Private Sub Worksheet_Activate() RowsCount = Me.UsedRange.Rows.Count End Sub Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo EH ' Detect whole row changed If Target.Columns.Count = Me.Columns.Count Then ' Detect Extra Row If RowsCount = Me.UsedRange.Rows.Count - 1 Then ' Copy Formulas and Format new row Application.EnableEvents = False If Target.Row > 1 Then Target.Offset(-1, 0).Copy Target.PasteSpecial xlPasteFormulas, xlPasteSpecialOperationNone, False, False Target.PasteSpecial xlPasteFormats, xlPasteSpecialOperationNone, False, False Application.CutCopyMode = False End If End If RowsCount = Me.UsedRange.Rows.Count End If EH: Application.EnableEvents = True End Sub
Похоже, вы привязаны к неправильному событию. Вы привязали свой код к событию «Worksheet_Change», но ваш код также вызывает изменение на листе
Cells(1, 2).Value = 10
Который поворачивается вправо и вызывает событие «Worksheet_Change».
Что касается подходящего события для присоединения, похоже, нет никакого родного события для «New Row Inserted».
На этой странице обсуждается это, что может быть ответом на вашу проблему …
Как @laughsloudly сказал, что ваш код делает сейчас, это когда вы делаете изменения в любом месте диапазона A1: D25, он начнет вставлять строки до тех пор, пока Excel не закончится из строк, это открытый цикл.
Код:
If Target.Range("A1:D25") = ActiveCell.EntireRow.Insert Then
Предназначен для проверки того, выполнено ли действие в пределах диапазона, который вы хотите контролировать. Вы не хотите выполнять действие в этой строке. Скорее, вы хотите что-то большее:
If Target.Range("A1:D25") = ActiveCell Then
Это позволит вам запускать код на основе действий на вашем листе. Тем не менее, ваше утверждение «Я пытаюсь, чтобы моя таблица автоматически принимала формат и формулы предыдущих строк при вставке новой строки». не совсем логично. Я предполагаю, что вы хотите скопировать все форматы из указанной строки и только формулы из определенных ячеек, правильно? Итак, допустим, если у вас есть строки, у всех есть формулы относительно столбца A, вы не хотите копировать всю строку, потому что вы будете перезаписывать A. Кроме того, в этом случае вам нужно только контролировать столбец A.
Итак, допустим, у вас есть формулы в столбцах B-K, которые полагаются на столбец A, тогда вы хотите, чтобы изменения в столбце A влияли на электронную таблицу. Тогда ваш код будет выглядеть примерно так:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = ActiveCell.Column Then refRow = Target.Row - 1 thisRow = Target.Row Range("B" & refRow & ":K" & refRow).Copy Range("B" & thisRow & ":K" & thisRow) End If End Sub
Это копирует все, кроме столбца A, когда вы вносите изменения в A. И, как уже упоминалось ранее, вы не хотите, чтобы код вносил какие-либо изменения в столбец A (в данном случае), без чего-либо, чтобы вырвать его из этого рекурсивного цикла , Лучше всего применить условное форматирование к любому столбцу, который вы назначаете в качестве цели.
Надеюсь, это поможет,
n8