User input Auto multiplies by constant

**vikhound** · 08-28-2009, 10:57 AM

Hi,
Heres the situation that I have been hitting my head on. I need to create three columns that if a user inputs a number, it automatically multiplies by that number by a cell reference. So if a user types in 3 in column A Row 3, it automatically mutiplies that number by cell reference $A$2.
Thanks for the help!
-Vik

**Palmetto** · 08-28-2009, 11:14 AM

So if a user types in 3 in column A Row 3, it automatically mutiplies that number by cell reference $A$2.

You cannot type a number into a cell and at the same time multiply it by a number in another cell without using VBA or having a formula cell that uses two input cells.

Perhaps you meant: multiply $A$2 by a number entered into A3? If so, then
the results cell uses this formula: = $A$2*A3

**royUK** · 08-28-2009, 11:20 AM

This code will work on Columns B,C,D

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column < 2 Or Target.Column > 4 Then Exit Sub
Target.Offset(-1, 0).Value = Target.Offset(-1, 0).Value * Cells(2, 1).Value
End Sub

Copy the code
Select the worksheet in which you want the code to run
Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
Where the cursor is flashing, choose Edit | Paste

**vikhound** · 08-28-2009, 11:50 AM

I tried that code and followed your instructions to the letter, but it came back with a run time error 424.

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub      

What does the Target.Count > 1 do?

If Target.Column < 2 Or Target.Column > 4 Then Exit Sub  

Does the above syntax set the range between columns 2 and 4?

Target.Offset(-1, 0).Value = Target.Offset(-1, 0).Value * Cells(2, 1).Value

Cells(2,1) does that refer to column 2, row 1? Also, Could I include If statements that changes that value based on the column (the reference cells for each column are different)?

End Sub

**royUK** · 08-28-2009, 12:07 PM

Attach the workbook with the code in

**Palmetto** · 08-28-2009, 12:20 PM

vikhound,

Obviously, disregard my post. For some reason, I must have thought I was in the General section of the forum.

Clearly, you intended to get a VBA solution.

**vikhound** · 08-31-2009, 09:55 AM

Ok so I attached the file to this forum reply. Just to resummarize;
In this file I need to create reference cells with fixed values into C2, D2, and E2 respectively. Then I want the user to be able to enter values into the corresponding columns (C5, D5, or E5 onwards) and have it automatically multiply by C2, D2, or E2. So if a user wants to edit the RAD ingredient in product C, all they do is input a number into the corresponding column C and it automatically multiplies by the value in C2. Also, Can you tell me how to increase the number of columns in case new ingredients need to be added?
Thanks alot for taking trouble,
Vik

**royUK** · 09-01-2009, 02:32 PM

I think this will do as you want

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LastRw As Long
    LastRw = ActiveSheet.UsedRange.Rows.Count
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    'this works on columns C,D,E. To add Columns change the 5 in this
    'Cells(LastRw, 5) to the column number required e.g. 8 would extend to H
    If Not Intersect(Target, Range(Cells(5, 3), Cells(LastRw, 5).End(xlUp))) Then
        Target.Value = Target.Value * Cells(2, Target.Column)
    End If
    Application.EnableEvents = True
End Sub