excel - 使用 workbook_sheetchange 事件在行范围内使数值为负

Question

我正在用 excel 创建预算，我复制并粘贴了一整年的每月预算大纲。某些支出在本工作簿的多个工作表中都位于同一行。我想这样做，每当在这些行中的单元格中输入数值时，它都会立即更改为负值。
这些行中有非数字文本，所以我希望 vba 只查找那些有数字的行中的单元格。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Rows("22:52")) Is Nothing Then 'VBA change event for all sheets
Dim rng As Range
        For Each rng In Intersect(Target, Rows("22:52"))
        If Application.WorksheetFunction.IsNumber(Rows("22:52")) Then
            If rng.Value > 0 Then c.Value = 0 - c.Values
                End If
            End If
        Next rng
End Sub

如果没有阻塞，我得到的错误是结束。

Answer 1

I want to make it so whenever a numerical value is entered into a cell in those rows, it is immediately changed to a negative value.

这是你正在尝试的吗？

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    On Error GoTo Whoa
    
    Application.EnableEvents = False
    
    Dim aCell As Range
    
    If Not Intersect(Target, Rows("22:52")) Is Nothing Then
        For Each aCell In Target
            If aCell.Value2 > 0 Then aCell.Value = -1 * aCell.Value2
        Next aCell
    End If
    
Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

备注 :如果您打算更改未激活的工作表的值，那么您将不得不执行 Tim 在上面的评论中提到的操作。例如，如果 Sheet1处于事件状态，您想写信给 Sheet2 .
改变

If Not Intersect(Target, Rows("22:52")) Is Nothing Then

至

If Not Intersect(Target, Sh.Rows("22:52")) Is Nothing Then