CODEWITHSUNDEEP
excelvba
abiieez
abiieez

Reputation: 3189

Excel multiply field by another column during key in

I have a B column which have many rows of record and by default are empty. Now I would like it if someone keys in any integer into the record lets say on cell B1, it should compute as

integer value of cell * (-20)

The -20 modifier is stored in lets say Z1. Any value keyed in in B1,B12... Bn should computed the same way as above. How can I do this ?

EDIT

Excpected result

enter image description here

Upvotes: 0

Views: 84

Answers (2)

chris neilsen
chris neilsen

Reputation: 53126

Yes, use the Change event, but you need to take a few things into account:

  1. What if the user pastes several values?
  2. What if the user enters a formula?
  3. What if the user deletes some data?

This version takes these things into account

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim cl As Range

    Application.EnableEvents = False
    Set rng = Intersect(Me.Columns(2), Target)
    If Not rng Is Nothing Then
        For Each cl In rng.Cells
            If Not IsEmpty(cl) Then
                If Not cl.HasFormula Then
                    cl.Value = cl.Value * Me.Range("Z1")
                End If
            End If
        Next
    End If
    Application.EnableEvents = True
End Sub

Upvotes: 3

ARich
ARich

Reputation: 3279

You can use the Change event for this. Paste this into the Sheet module of the sheet you want to use this on:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B:B")) Is Nothing Then
    Target = Target.Value * Range("Z1")
End If

End Sub

The Intersect method tests to make sure the changed cell is in Column B. Target is the cell whose value was changed.

Upvotes: 3

Related Questions