Automatically multiply a value

[Frantic]

Hello

I want to be able to enter a value into a cell (within a specified range if necessary) and have it automatically multiply by a set value and overwrite the original entry with the result so making it all happen within one cell.

Is this possible?

Thanks
F

 

[xld]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"     '<== change to suit
Const SET_VALUE As Long = 23            '<== change to suit

    On Error GoTo ws_exit
    Application.EnableEvents = False

    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
    
        With Target
            
            If IsNumeric(.Value) Then .Value = .Value * SET_VALUE
        End With
    End If

ws_exit:
    Application.EnableEvents = True
End Sub

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

 

[Frantic]

Thank you, this is really helpful. I've just got a bit stuck because the value I want to multiply by is 0.163 but when I put this in the code it just returns 0. The code works fine when I put in the value you gave me (23). Do I need to something different for a value that's less than 1?

 

[Yard]

Are you sure your cell formatting is set to display decimal places?

 

[Frantic]

Actually, they weren't formatted for decimal places but it still isn't working! What even value I enter it now just shows 0.00. Any other ideas?

Update: if I enter a value with a decimal into the code it seems to round it up/down. It doesn't seem to cope with decimal values. Is there a fix for this?

 

[jim may]

With your Const SET_VALUE As Long = 0.163

Provide a couple examples of What values you are entering, please.

 

[Frantic]

I've just been putting any old number at random, it doesn't make any difference what number I put in it still enters 0.00 when it's multiplying it by 0.163. It's rounding it down to 0, hence why the answer is 0.00.

If I change 0.163 to, for example 1.9, it's multiplys everything by 2 (not 1.9)

 

[Yard]

oops, shouldn't it be

Const SET_VALUE As Double = 0.163

 

[xld]

Thank you, this is really helpful. I've just got a bit stuck because the value I want to multiply by is 0.163 but when I put this in the code it just returns 0. The code works fine when I put in the value you gave me (23). Do I need to something different for a value that's less than 1?

Just change the data type of that constant to Double not Long.

 

[Frantic]

Hooray! Thank you, that does it! i really appreciate your help :D