Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formulas
I want to enter a value in a cell and have it automatically add a
predetermined value to it. I'm using excell 2003 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formulas
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 -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "chuck" wrote in message ... I want to enter a value in a cell and have it automatically add a predetermined value to it. I'm using excell 2003 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formulas
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 "Bernard Liengme" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "chuck" wrote in message ... I want to enter a value in a cell and have it automatically add a predetermined value to it. I'm using excell 2003 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formulas
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 -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "chuck" wrote in message ... 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 "Bernard Liengme" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "chuck" wrote in message ... I want to enter a value in a cell and have it automatically add a predetermined value to it. I'm using excell 2003 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
Copying formulas from Excel 2003 to Excel 2007 | Excel Discussion (Misc queries) | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
Excel Formulas | Excel Discussion (Misc queries) | |||
excel formulas | Excel Discussion (Misc queries) |