Excel formulas

  • Thread starter Thread starter chuck
  • Start date Start date
C

chuck

I want to enter a value in a cell and have it automatically add a
predetermined value to it. I'm using excell 2003
 
This is a modification of one of Bernie Deitrick's replies earlier this week

Copy the code below, right-click the sheet tab, select "View Code" and paste
the code into the
window that appears.

Change the
Set rngPCode = Range("D:D")
to the column where the numbers will be entered.
This could be just a single cell as: Set rngPCode = Range("A2")
or a range as in: Set rngPCode = Range("B1:B10")

Also change: MyValue = 3.142 to reflect the value you want to add


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngPCode As Range
Dim MyValue As Double
MyValue = 3.142
Set rngPCode = Range("D:D")
If Intersect(rngPCode, Target) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo Reset
Application.EnableEvents = False
Target.Value = Target.Value + MyValue
Reset:
Application.EnableEvents = True
End Sub

best wishes
 
Thanks Bernard your response was very helpful. Now I've got another question.
If I want the the amount added to vary based upon the value that I input, is
that possible?

i.e. less
4 or less add .25
4 to 6 add .50
6 to 9 add .75
 
Try this
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngPCode As Range
Dim MyValue As Double
Set rngPCode = Range("D:D")
If Intersect(rngPCode, Target) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo Reset
Application.EnableEvents = False
MyValue = 0
If Target.Value <= 4 Then
MyValue = 0.25
ElseIf Target.Value < 6 Then
MyValue = 0.5
ElseIf Target.Value < 9 Then
MyValue = 0.75
End If
Target.Value = Target.Value + MyValue
Reset:
Application.EnableEvents = True
End Sub

You should test it. I expect you will be able to modify it as needed
You may need: ElseIf Target.Value <= 6 Then
for less than or equal to 6
best wishes
 
Back
Top