How would I write a VB code to do the following:

a value changes in cell E10. I want to empty the contents of cell L10.
I want to continue this for a span of some 3000 rows for whenever a value changes in column E.


Also... would I add a new VB module for this or can I add this to the existing module I already have in the same worksheet? (from a recent previous post)

Thank you,
Melinda

Hi Melinda

This is just an addition to the module you already have from before.
Just add this to your current worksheet_change sub routine.

this will work for all of column E. If you want to restrict this to a set
number of rows then change this to

the example above works between row 10 and row 3010.

Finally if you don't know how many rows you want this in you can set up
variables to work this out.

David

Thank you!!!

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.
Thank you,
Melinda

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 Hope that helps,

Smitty

no, the number will not be the same, it will be a different value, or no value, in each cell within the range.

How's this then:

Cells(Target.Row, 12).Value = Cells(Target.Row, 12).Value + Cells(Target.Row, 13).Value

Change to column number to suit.

Smitty

Originally posted by Smitty
How's this then:

Cells(Target.Row, 12).Value = Cells(Target.Row, 12).Value + Cells(Target.Row, 13).Value

Change to column number to suit.

My original values would be in colum 12, the new additive values would be typed in column 13 and the new total returned in column 12? Is that how I read that?

Yes

Is there a way to do it all within one cell:
My original values would be in colum 12, the new additive values would be typed in column 12 and the new total returned in column 12?

Not to my knowledge.

But you could use the InputBox method that I posted earlier. It requires the same number of keystrokes as entering data directly into the cell.

Smitty

Thanks Smitty, I will tryout the InputBox method, that seems to be the way my user wants to go.
Melinda

Originally posted by qimel Thanks Smitty, I will tryout the InputBox method, that seems to be the way my user wants to go.
Melinda

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")
End If

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

Melinda

I was just Looking at what you were saying.

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

Hope this Helps

David