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

问题描述

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

Im currently trying to implement an insert new row value and a automatic checkbox inserter.

我目前有以下代码分布在不同的按钮上，因此分布在不同的Subs上.我已将需要增加1个单元格的关键信息加粗.单击"InsertNewBill"按钮后，将发生这种情况.

I currently have the following code spread over different buttons and therefore different Subs. I have boldened the key information that i will need to increment by 1 cell. This will occur after clicking the "InsertNewBill" button.:

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子项.

I need all boldened values to increase by one. I.e from F30 to F31 and A30:AC30 to A31:AC31.This value also needs to be carried across from the InsertNewBill_Click sub to the DeleteTickBoxes_Click sub.

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

I assume i will need to remove the Private sub and possibly have a public integer variable?Im just not sure how to implement increasing only the number by 1 after each button click.

感谢您的帮助

推荐答案

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 是3个范围的 Union()；
• myCell 是一个范围，用于遍历 unionRange ;
中的所有单元格
• myCell = myCell + 1 将值增加1.
• 如果myCell.Font.Bold然后检查单元格是否粗体.
• unionRange is a Union() of the 3 ranges;
• myCell is a Range and used to loop through all the cells in unionRange;
• myCell = myCell + 1 increments the value by 1.
• If myCell.Font.Bold Then checks whether the cell is bold.

这篇关于使用VBA Excel将范围内的单元格值增加1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！