excel - 需要在一个范围内的活动单元格中将值 1 增加 1

Question

Sub Macro5()
  Dim rng As Range
    Set rng = Selection
      For Each cell In rng
        ActiveCell.Value = ActiveCell.Value + 1
     Next
End Sub

标签： excelvba

Answer 1

Quick fix for your code would be

Sub Macro5()
    Dim rng As Range
    Set rng = Range("B2:B10")
    Dim cell As Range
    For Each cell In rng
        cell.Value = cell.Value + 1
    Next
End Sub

Update: By the comment I guess you would like to use the SelectionChange Event. Put the following code into the code module of the sheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    On Error GoTo EH
    Application.EnableEvents = False

    Dim rg As Range
    Set rg = Range("B2:B12")

    If Not (Intersect(rg, Target) Is Nothing) Then
        Dim sngCell As Range
        ' This will only increase the values of the selected cells within B2:B10
        ' Not sure if this is wanted. Otherwise just modify according to your needs
        For Each sngCell In Intersect(Target, rg)
            sngCell.Value = sngCell.Value + 1
        Next sngCell
    End If

EH:
    Application.EnableEvents = True
End Sub

Update 2: If you want to run the code via a button put the following code into a standard module and assign it to a button you create on the sheet

Sub Increase()

    On Error GoTo EH
    Application.EnableEvents = False

    Dim rg As Range
    Set rg = Range("B2:B10")

    If Not (Intersect(rg, Selection) Is Nothing) Then
        Dim sngCell As Range
        For Each sngCell In Intersect(Selection, rg)
            sngCell.Value = sngCell.Value + 1
        Next sngCell
    End If

EH:
    Application.EnableEvents = True
End Sub