Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2004
    Location
    USA - California
    Posts
    21

    Unanswered: VB code to empty cell

    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

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067

    Re: VB code to empty cell

    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.

    Code:
        If Target.Column = 5 Then
            Cells(Target.Row, 12).Formula = ""
        End If
    this will work for all of column E. If you want to restrict this to a set
    number of rows then change this to

    Code:
        If Target.Column = 5 And Target.Row >= 10 And Target.Row <= 3010 Then
            Cells(Target.Row, 12).Formula = ""
        End If
    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

  3. #3
    Join Date
    Feb 2004
    Location
    USA - California
    Posts
    21

    Re: VB code to empty cell

    Thank you!!!

  4. #4
    Join Date
    Feb 2004
    Location
    USA - California
    Posts
    21

    Question Add values to cell with existing values

    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

  5. #5
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    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
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Column = 5 Then
            Cells(Target.Row, 12).Value = Cells(Target.Row, 12).Value + 5
        '   Or
            '   Cells(Target.Row, 12).Value = Cells(Target.Row, 12).Value + _
            InputBox("Please enter the value by which to increment", "Increment Cell Value")
        End If
    End Sub
    Hope that helps,

    Smitty

  6. #6
    Join Date
    Feb 2004
    Location
    USA - California
    Posts
    21
    no, the number will not be the same, it will be a different value, or no value, in each cell within the range.

  7. #7
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    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

  8. #8
    Join Date
    Feb 2004
    Location
    USA - California
    Posts
    21
    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?

  9. #9
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    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

  10. #10
    Join Date
    Feb 2004
    Location
    USA - California
    Posts
    21
    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?

  11. #11
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    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

  12. #12
    Join Date
    Feb 2004
    Location
    USA - California
    Posts
    21
    Thanks Smitty, I will tryout the InputBox method, that seems to be the way my user wants to go.
    Melinda

  13. #13
    Join Date
    Feb 2004
    Location
    USA - California
    Posts
    21

    Help SMitty......

    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

  14. #14
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067

    Re: Help SMitty......

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •