Can I do this:
Say I have a range of cells B4:B3000 and they contain values. I want to add to those values by typing the increment in the cell that already contains the base value. So.....Cell B10 contains 10. I highlight the cell and type 5 and when I hit enter I want the result in B10 to then be 15. Is there a way VBA can do that? I know I can write a macro to copy the existing values to a "holding range" and add the new inputs to them, but that will make for a cumberson & large spreadsheet.
If the number that you're adding will be the same, you can put it in a blank cell and Copy-->Select the range to add the number to and Edit-->Paste Special-->Add. Or
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
Cells(Target.Row, 12).Value = Cells(Target.Row, 12).Value + 5
' Cells(Target.Row, 12).Value = Cells(Target.Row, 12).Value + _
InputBox("Please enter the value by which to increment", "Increment Cell Value")
Originally posted by qimel Thanks Smitty, I will tryout the InputBox method, that seems to be the way my user wants to go.
Smitty....I am not doing something right...this is new to me. It almost works but I get an error after the first time. This is what I have: (The column I want to increment is K)
If Target.Column = 11 And (Target.Row > 2 < 5001) Then
Cells(Target.Row, 11).Value = Cells(Target.Row, 11).Value + InputBox ("text goes here","title here")
So, I go to K9 and there is already 11 in the cell. I type 5 enter. The cell value changed to 5 and the cursor drops to K10. I don't want that to happen, I want it to stay in K5, but let's go on. my increment cell value window pops up and asks me to enter a value. I again enter 5 in the increment window, it adds it back to K9 as I wanted, but against the wrong starting number in K9 because it took my initial value input and changed it so I now have a value of 10 in K9..... it should be 16. See? I am sorry, probably something very simple I am doing wrong.
Thank you, Melinda
You had 11 in a cell. then typed 5 into that cell.
then your macro kicks in and asks you to input a number.
you chose 5.
so you get 5 + 5 = 10
as the macro runs after the cell has changed.
What you could do is use the Worksheet_beforedoubleclick event
so if you want to increment te cell you have to double click on it