为什么我的Excel代码会更改整个表中的引用,而不是仅更改添加的新行?

9q78igpj  于 2023-10-21  发布在  其他
关注(0)|答案(1)|浏览(73)

我有一个新的代码,这是应该添加一个新的行表“排名”在工作表“ Jmeter 板”每当一个新的工作表被添加,然后链接的第一和第二个细胞在新的行中的特定细胞在新的工作表。创建一个新行并链接到正确的单元格工作正常,但每次我添加一个新工作表时,它也会将表格中的其他现有行更改为新工作表中的单元格。
假设table是:
| 列A|色谱柱B|
| --|--|
| 1 | 2 |
| 3 | 4 |
当我添加一个新表时,它会变成:
| 列A|色谱柱B|
| --|--|
| 5 | 6 |
| 5 | 6 |
| 5 | 6 |
我想要的是:
| 列A|色谱柱B|
| --|--|
| 1 | 2 |
| 3 | 4 |
| 5 | 6 |
你知道为什么会这样吗?有没有办法解决这个问题?
代码为:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim RankingsTable As ListObject
    Dim NewRow As ListRow

    ' Reference the "Rankings" table in the "Dashboard" sheet
    On Error Resume Next
    Set RankingsTable = ThisWorkbook.Sheets("Dashboard").ListObjects("Rankings")
    On Error GoTo 0

    ' Check if the active sheet is not the "Dashboard" sheet
    If Sh.Name <> "Dashboard" Then
        ' Add a new row to the "Rankings" table if it exists
        If Not RankingsTable Is Nothing Then
            Set NewRow = RankingsTable.ListRows.Add
            ' Set the values in the new row based on the references you mentioned
            NewRow.Range.Cells(1, 1).Formula = "='" & Sh.Name & "'!R5C3"
            NewRow.Range.Cells(1, 2).Formula = "='" & Sh.Name & "'!R30C3"
        End If
    End If
End Sub
kokeuurv

kokeuurv1#

可以使用Application.AutoCorrect.AutoFillFormulasInLists
为了跟踪原始设置,它存储在ThisWorkbook模块的私有变量中
通过在每个列表行插入之前和之后调用checkAutoFillFormulasInLists,可以避免您所描述的“错误”。

Option explict
Private m_UserSettingAutoFillFormulasInLists As Boolean

Private Sub Workbook_Open()
    m_UserSettingAutoFillFormulasInLists = Application.AutoCorrect.AutoFillFormulasInLists
End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim RankingsTable As ListObject
    Dim NewRow As ListRow

    ' Reference the "Rankings" table in the "Dashboard" sheet
    On Error Resume Next
    Set RankingsTable = ThisWorkbook.Sheets("Dashboard").ListObjects("Rankings")
    On Error GoTo 0
    
    
    ' Check if the active sheet is not the "Dashboard" sheet
    If Sh.Name <> "Dashboard" Then
        ' Add a new row to the "Rankings" table if it exists
        If Not RankingsTable Is Nothing Then
            checkAutoFillFormulasInLists False
                Set NewRow = RankingsTable.ListRows.Add
                ' Set the values in the new row based on the references you mentioned
                NewRow.Range.Cells(1, 1).Formula = "='" & Sh.Name & "'!R5C3"
                NewRow.Range.Cells(1, 2).Formula = "='" & Sh.Name & "'!R30C3"
            checkAutoFillFormulasInLists True
        End If
    End If
End Sub

Private Sub checkAutoFillFormulasInLists(fValue As Boolean)

If fValue = False And m_UserSettingAutoFillFormulasInLists = True Then
    Application.AutoCorrect.AutoFillFormulasInLists = False
Else
    Application.AutoCorrect.AutoFillFormulasInLists = m_UserSettingAutoFillFormulasInLists
End If

End Sub

相关问题