Excel multiply field by another column during key in

Question

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

This is possible using events. Are you somehow familiar with macro or somewhat inclined to it? If not, then I can't think of a work around without vba. — L42, Feb 23 '14 at 06:06
The question has pushed people to respond with VBA code because of the specification that the cell modified by the user should be the very same one over-written with another value. Maybe there is a need for the spreadsheet to act that way but the user experience is likely to be very frustrating. Can you imagine working with a spreadsheet in which the values you type into a cell don't stick but are over-written as soon as you press Enter? Is it not possible/preferable to set up the spreadsheet so that the user enters a value in col B and the value multiplied by Z1 appears next to it in Col C? — MattClarke, Feb 23 '14 at 23:43

score 3 · Answer 1 · edited May 08 '18 at 21:48

3

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.

edited May 08 '18 at 21:48

Community

1
1

answered Feb 23 '14 at 06:05

ARich

3,230
5
30
56

I am very new to this, where do I open the Sheet module ? – abiieez Feb 23 '14 at 06:09
See my edited answer for a link about modules. Use `Alt` + `F11` to open the VBEditor, then locate the sheet in the project explorer on the left (`Ctrl` + `R` if it isn't visible) and double click your sheet name to open it. – ARich Feb 23 '14 at 06:13

score 3 · Accepted Answer · answered Feb 23 '14 at 06:12

3

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

What if the user pastes several values?
What if the user enters a formula?
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

answered Feb 23 '14 at 06:12

chris neilsen

52,446
10
84
123

@chris neilsen although all scenarios were seemed covered, i think an error handler is still valuable since you are turning events on and off :) – L42 Feb 23 '14 at 06:20
@L42 Error handling is (almost) always advisable :) I don't see why turning Events off would affect that? And I just thought of another potential issue: what is the user enters non-numeric data? – chris neilsen Feb 23 '14 at 06:45
@chrisneilsen ah, cause if error occured somewhere, event will not be turned back on again. :) See [THIS](http://stackoverflow.com/questions/21519451/auto-get-value-in-excel-vba/21520211#21520211). Well just a thought. :D – L42 Feb 24 '14 at 00:55

Excel multiply field by another column during key in

2 Answers2