+ Reply to Thread
Results 1 to 12 of 12

Using VB code to check for negative values then subtract them from a neighboring cell

  1. #1
    Registered User
    Join Date
    05-05-2009
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    68

    Question Using VB code to check for negative values then subtract them from a neighboring cell

    Hi

    I would like to use VB code to compare 2 columns of cells and in any instances where there is a negative value in lets say column B I'd like this value to be subtracted from Column A

    This code will later be attached to a Button if possible

    Example

    ..A . B < Column letters in bold

    1 1 2
    2 2 -2
    3 2 0
    4 4 1
    5 3 -1
    6 2 0

    ^ Row numbers in bold

    In this example cell value B2 (-2) should be subtracted from A2 and Cell value B5 (-1) should be subtracted from value A5

    After this calculation is performed if any negative values in column B could be reset to 0 that would be great


    Thanks in advance the help I have received on this site so far has been amazing
    Last edited by mick86; 07-14-2012 at 09:44 AM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Using VB code to check for negative values then subtract them from a neighboring cell

    This should go to the programming for, if that is what you're after.

    The VBA on this could go two routes:

    1) Loop and check each row one at a time:

    Please Login or Register  to view this content.


    2) Use formulas to update both columns all at once.
    Please Login or Register  to view this content.

    My preference would be for method 2.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    05-05-2009
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    68

    Re: Using VB code to check for negative values then subtract them from a neighboring cell

    When I try to implemented that code it seems to work horizontally across the page in some fashion rather than down. It might just be me so I have included a simplified version of my file.

    G column which I've highlighted orange is where there are some negative values

    E column which I've highlighted in red is where id like the negative values to me subtracted from the values

    I also through in a huge button
    Attached Files Attached Files

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Using VB code to check for negative values then subtract them from a neighboring cell

    That's an xlsx file so I know there's no code in it. For me to comment on your attempts to adapt the techniques given I need to see your attempt(s).

  5. #5
    Registered User
    Join Date
    05-05-2009
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    68

    Re: Using VB code to check for negative values then subtract them from a neighboring cell

    I purposely left it blank because even when I applied the code to a blank workbook it appeared to act in a manner that I didn't expect placing a bunch of zeros across many columns instead of just in 2 columns down many rows

    I thought my example file might express my problem better

    When I modified it I changed all your A column references to E and all the B Column references to G as per my files layout

    I also changed the row 1 references to 2 as my data begins in row 2

    But again even in a blank workbook I was a little puzzled by the outcome of running the script as it's written so I thought that perhaps I hadn't described my desired outcome clearly thus the example file


    EDIT

    Retried with a blank file with data set up in columns A and B and code works

    My attempt to convert it must have been the issue, I did it in a rush before heading out to dinner and must have really messed it up

    Thank you kindly for your expertise


    Modified code is simply:


    Please Login or Register  to view this content.
    Last edited by mick86; 07-14-2012 at 02:54 PM.

  6. #6
    Registered User
    Join Date
    05-05-2009
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    68

    Re: Using VB code to check for negative values then subtract them from a neighboring cell

    Only thing left to do is stop the code at row 39 is there as simple trick to achieve this?

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Using VB code to check for negative values then subtract them from a neighboring cell

    Please ignore my "edit code tags" comments above, my system is reading these posts all wrong and I apologize for that. *groan*


    Anyway, since the data isn't adjacent, you must copy back to the original locations as separate commands.

    Please Login or Register  to view this content.


    Or, if you really only have a specific range, then:
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    05-05-2009
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    68

    Re: Using VB code to check for negative values then subtract them from a neighboring cell

    Please Login or Register  to view this content.
    Apologies regarding code tags

    I have found that the following code works which I derived from your work

    I attached a functioning example file
    Attached Files Attached Files
    Last edited by mick86; 07-15-2012 at 01:07 AM.

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Using VB code to check for negative values then subtract them from a neighboring cell

    You should probably use my code from post #7, your single command is copying all the empty columns in between and may delete the data might have between columns E and G.

    If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.
    Last edited by JBeaucaire; 07-15-2012 at 04:10 AM.

  10. #10
    Registered User
    Join Date
    05-05-2009
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    68

    Re: Using VB code to check for negative values then subtract them from a neighboring cell

    Sorry the post numbers have now changed

    Do u mean this piece of code?

    Please Login or Register  to view this content.
    Are you saying that it will work as it is or does it still need to be modified? I tried it as it is and it seems to need alterations.

  11. #11
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Using VB code to check for negative values then subtract them from a neighboring cell

    Yes, that is the code I'm suggesting is more accurate.

  12. #12
    Registered User
    Join Date
    05-05-2009
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    68

    Re: Using VB code to check for negative values then subtract them from a neighboring cell

    You were right about loosing date from the column in between

    I wasn't able to modify the code to fix it but I removed the in between column and changed the code I was currently using to suit that.

    Thank you kindly for all of your help

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1