使用 VBA Excel 将范围内的单元格值增加 1

我目前正在尝试实现插入新行值和自动复选框插入器。

我目前将以下代码分布在不同的按钮上，因此分布在不同的子按钮上。我已将需要增加 1 个单元格的关键信息加粗。这将在单击“InsertNewBill”按钮后发生：

Private Sub InsertNewBill_Click()
    'I AM USING i TO STORE THE CELL INCREMENT, IT CURRENTLY DOES NOTHING**
    Dim i As Integer
    '**range("A30:AC30").Select**
    '**range("AC30").Activate**
    Selection.Copy
    Selection.Insert Shift:=xlDown
End Sub

Private Sub DeleteTickBoxes_Click()
    'Variables
    Dim c As CheckBox
    Dim CellRange As Range
    Dim cel As Range
    Set CellRange = ActiveSheet.Range("E7:**F30**")    
    'Delete Checkboxes within the specified range above on the ActiveSheet Only
    For Each c In ActiveSheet.CheckBoxes
        If Not Intersect(c.TopLeftCell, CellRange) Is Nothing Then
            c.Delete
        End If
    Next    
    'Insert New Checkboxes and Assign to a specified link cell using the offset
    For Each cel In CellRange
        'you can adjust left, top, height, width to your needs
        Set c = ActiveSheet.CheckBoxes.Add(cel.Left, cel.Top, 30, 6)
        With c   'Clears the textbox so it has no text
            .Caption = ""
            'Offset works by offsetting (Row offset, Column Offset) and accepts
            'positive for down/right and negative for left/up,
            'keep in not that the linked cells will automatically populate with true/false
            .LinkedCell = cel.Offset(0, -4).Address
        End With
    Next
    Call CentreCheckbox_Click
End Sub

我需要将所有加粗的值加一。即从 F30 到 F31 以及 A30:AC30 到 A31:AC31。 该值还需要从InsertNewBill_Click 子项传递到DeleteTickBoxes_Click 子项。

我假设我需要删除 Private sub 并且可能有一个公共整数变量？ 我只是不确定如何在每次单击按钮后仅将数字增加 1。

感谢您的所有帮助

Sub TestMe()

    Dim unionRange As Range
    Dim ws As Worksheet
    Set ws = Worksheets(1)

    With ws
        'as an alternative -> Set unionRange = ws.Range("A30:AC31")
        Set unionRange = Union(.Range("F30:F31"), .Range("A30:AC30"), .Range("A31:AC31"))
    End With

    Dim myCell As Range
    For Each myCell In unionRange
        If myCell.Font.Bold Then
            myCell = myCell + 1
        End If
    Next

End Sub

• unionRange is a Union()3 个范围中的一个；
• myCell是一个范围，用于循环遍历所有单元格unionRange;
• myCell = myCell + 1将值增加 1。
• If myCell.Font.Bold Then检查单元格是否bold.