Results 1 to 2 of 2

Thread: Absolute amount or IF and Greater than Formula Code Required

  1. #1
    Member
    Join Date
    Jul 2013
    Posts
    31
    Rep Power
    0

    Absolute amount or IF and Greater than Formula Code Required

    Hi, need some help here in the below code.

    This code throws up a comment 'DISPUTE AMT MISMATCH' when the amt in a particular column is more than 1,000,000 (1million) However, i have amounts that are sometimes in the negative side and still more than a million. For e.g. (-25,000,000 or -5,000,000) and it doesnt populate the comment.

    What formula should i apply so that it still throws up the comment 'DISPUTE AMT MISMATCH'?

    Code:
    Range("A2", Range("A" & Rows.Count).End(xlUp)).Offset(, 24).FormulaR1C1 = _
    "=IF(RC[11]>1000000,""DISPUTE AMT MISMATCH"",""OK"")"

    else, can i have an absolute amount as a result of the below code which would automatically make all the amouts positive and then my above code would not require any change.


    Code:
    Range("AF2", Range("AF" & Rows.Count).End(xlUp)).Offset(, 3).FormulaR1C1 = _
    "=ABS(RC[-3]-RC[-1])-RC[-19]"

    Thanks

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    You could also try this

    Code:
    Range("A2", Range("A" & Rows.Count).End(xlUp)).Offset(, 24).FormulaR1C1 = _
    "=IF(abs(RC[11])>1000000,""DISPUTE AMT MISMATCH"",""OK"")"
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Replies: 12
    Last Post: 07-31-2013, 05:44 PM
  2. Replies: 13
    Last Post: 07-08-2013, 11:28 PM
  3. Replies: 2
    Last Post: 12-19-2012, 11:52 PM
  4. Replies: 1
    Last Post: 12-04-2012, 05:30 PM
  5. Absolute Calender Program!
    By Preeti Verma in forum Excel Help
    Replies: 1
    Last Post: 11-06-2012, 01:19 PM

Tags for this Thread

Posting Permissions

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