CODEWITHSUNDEEP
excelvba
renguer0
renguer0

Reputation: 17

Apply divide formula to entire column in macro on Excel?

I want to apply this formula not only to B2 cell but the entire B column.

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

Any kind of help will very appreciated.

Upvotes: 0

Views: 355

Answers (1)

Tim Williams
Tim Williams

Reputation: 166511

Something like this:

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

(edited to show use of Table/ListObject)

Upvotes: 1

Related Questions