excel - 将除法公式应用于Excel宏中的整列？

Question

我想将此公式不仅应用于 B2 单元格，而且应用于整个 B 列。

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target = [B2] Then
    Dim amount As Long
    amount = [B2]

    Application.EnableEvents = False
    Target.Value = amount / (100000000)
    Application.EnableEvents = True
  End If
End Sub

任何形式的帮助将不胜感激。

标签： excelvba

Answer 1

像这样的东西：

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim rng As Range, c As Range, rngCol As Range

  'Get the range from the table column header
  Set rngCol = Me.ListObjects("myTable").ListColumns("Col2").DataBodyRange

  'EDIT: alternative for multiple contiguous columns
  Set rngCol = Me.Range("myTable[Col2]:myTable[Col4]")

  'any changes in the monitored column?
  Set rng = Application.Intersect(Target, rngCol)

  If Not rng Is Nothing Then '<< got some changes in ColB
      Application.EnableEvents = False
      For Each c In rng.Cells
          If IsNumeric(c.Value) Then 
              'Update: only apply to values >1
              If c.Value > 1 Then c.Value = c.Value / 100000000
          End If
      Next c
      Application.EnableEvents = True
  End If

End Sub

（编辑以显示 Table/ListObject 的使用）