插入Excel VBA以在新行中运行宏 [英] Excel VBA to run Macro in new row is Inserted

问题描述

我正在尝试让我的电子表格在插入新行时自动采用以前的行格式和公式.

I am trying to have my spreadsheet automatically take the previous rows format and formulas when a new row is inserted.

我阅读了您可以在哪里设置工作表以在进行更改时自动运行代码的信息，但是我很难使代码正常工作.

I read where you can set up your sheet to automatically run the code if a change is made, but I am having a hard time getting the code to work.

我尝试了以下操作，每次插入新行时，它都会不断添加行，直到出现错误，然后我必须强制退出:

I have tried the following and every time I insert a new row it keeps adding a row until it gets an error and I have to force quit:

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以查看它是否可以工作.这只是一个测试，但仍然失败.

I added the Cell Value = 10 to see if it would work. It was just a test, but it still fails.

有人知道可能的解决方案吗?

Does anyone know a possible solution?

推荐答案

您的代码中存在两个主要问题

There are two main issues in your code

1. 您正在导致事件级联.也就是说，您的变更事件正在触发进一步的变更事件
2. .Insert并没有执行您似乎认为的操作.它不会检测插入的行，而是插入行.

1. You are causing an Event Cascade. Ie your Change event is triggering further change events
2. .Insert doesn't do what you seem to think it does. It doesn't detect inserted rows, it Inserts rows.

我假设是"...插入新行..." ，您的意思是插入整行

I am assuming by "... insert a new row ..." you mean Insert a whole row

此演示避免使用.EnableEvents = False级联，并使用Copy，pasteSpecial复制格式和公式.

This demo avoids the cascade with .EnableEvents = False and uses Copy, pasteSpecial to copy formats and formulas.

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

这篇关于插入Excel VBA以在新行中运行宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！