+ Reply to Thread
Results 1 to 5 of 5

vba for difference btw 2 numbers

  1. #1
    Registered User
    Join Date
    01-03-2013
    Location
    Saudi Arabia
    MS-Off Ver
    Excel 2007
    Posts
    28

    vba for difference btw 2 numbers

    i have an excel sheet1 with the following values

    I want to write a macro to do the following:

    (col A only has positive values, Col B has both positive and negetive values) I want to find the difference between the +ve and -ve values, keeping in mind the following;
    if col A's value is less than col B's Values, (Eg: 1 -3) the result should be -2
    if col A's value is equal than col B's Values, (Eg: 1 -1) the result should be 0
    if col A's value is less than col B's Values, (Eg: 1 3) the result should be 2


    QtyA QtyB
    1 -1
    1 -2
    2 -2
    3 1
    1 -5
    1 -3
    5 2
    1 1
    2 3
    5 -2
    1 1
    1 -1
    6 -5
    7 9
    8 3


    I want Col D to have the difference between the ColA And Col B.


    The result should be this:

    QtyA QtyB Result
    1 -1 0
    1 -2 -1
    2 -2 0
    3 1 2
    1 -5 -4
    1 -3 -2
    5 2 3
    1 1 0
    2 3 1
    5 -2 3
    1 1 0
    1 -1 0
    6 -5 1
    7 9 2
    8 3 5

  2. #2
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: vba for difference btw 2 numbers

    No VBA needed, you can use the formula below

    Please Login or Register  to view this content.
    If you like my contribution click the star icon!

  3. #3
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: vba for difference btw 2 numbers

    Another way (without IF):

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    01-03-2013
    Location
    Saudi Arabia
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: vba for difference btw 2 numbers

    im sorry i forgot to mention.....if both the numbers are equal i want to print "same"or "noChange".
    eg. 2 2 same
    1 1 same

    How do i do that along with the above mentioned calculation???

  5. #5
    Registered User
    Join Date
    01-03-2013
    Location
    Saudi Arabia
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: vba for difference btw 2 numbers

    ok i found it. i just tweaked your formula like this

    =IF($B1=$D1,"same",IF($B1<$D1,$D1-$B1,$B1-ABS($D1)))

    and its working. Thanks a lot . MY problem is solved

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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