Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Nov 2014
    Posts
    2
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Converting numerical cell value to absolute or negative/positive

    My bank (Chase) doesn't offer an Excel download of activity. I can get OFX or CSV, but the number values are negative. I want to use them in a simple spreadsheet, and need a way to convert the actual content from negative to positive or absolute, WITHOUT maintaining a separate reference cell for each number.

    I've tried ABS and CONVERT, but both require a reference cell. This makes it hard to use and manipulate data for 150-200 transactions per month.

    Is there a way to automatically convert an actual cell value? I'm looking for a macro, or an IF statement or a command that would do what Chase seems unable to do. (My other banks and cards offer Excel options.)

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Mike,

    This should do the trick:
    Code:
    Option Explicit
    
    Sub CvtToPositive()
    
       Dim rngCurrent  As Range
       
       Set rngCurrent = Range("D2")   '*** Set to your 1st value cell ***
       
       Do
         If rngCurrent.Value < 0 Then _
            rngCurrent.Value = _
            Abs(rngCurrent.Value)
            
         Set rngCurrent = rngCurrent.Offset(1, 0)
         
       Loop Until rngCurrent.Value = ""
       
    End Sub
    Before:
    MRBefore.JPG
    After:
    MRAfter.JPG
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    MikeRansom (2014-11-09)

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Perhaps changing the line:

    Code:
    Set rngCurrent = Range("D2")
    
    to
    
    Set rngCurrent = ActiveCell
    might make it a little easier than changing the start cell in the code. Simply select the first cell and run the maceo

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    MikeRansom (2014-11-09)

  6. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,188
    Thanks
    47
    Thanked 984 Times in 914 Posts
    Are the negative values debits? If so, change your spreadsheet to reflect the fact.

    cheers, Paul

  7. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Paul,

    My guess is that he already has the numbers in a debit column which is why they need to be positive numbers. I would seem that a negative debit equates to a double negative

  8. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Mike,

    Put -1 in some empty cell. Copy it to the clipboard.

    Now select the cells that are negative that you want to change. Next go to Edit | Paste Special. At the bottom, click the button for "Multiply". Click ok.

    Done!

    If you want, delete the -1 from the cell in the first step.

    Fred

  9. The Following User Says Thank You to fburg For This Useful Post:

    MikeRansom (2014-11-09)

  10. #7
    New Lounger
    Join Date
    Nov 2014
    Posts
    2
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Great thanks to RetiredGeek and Maudibe! The macro works perfectly (and easier with the automated "choose a start point" feature, which also allowed the macro to continue past an empty cell, vs stopping at the first void).

    I also found that highlighting the range, and using Ctrl-H (replace "-" with naught) worked too. But I like the speed and finesse of the macro.

  11. #8
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Although I am using my chase account to just park some money due to a promotion and have NO negatives, I did download the csv> put in a - in front of and entry and ran this one liner to remove the - in front of the figures. Question is why you need to convert from - to plus. Now all are plus.?????? More explanation.

    Columns("D").Replace "-", "", LookAt:=xlPart

  12. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Mike

    Without using a macro,
    >> click the column heading that contains the data (to highlight the entire column), then
    >> press Ctrl-H (this brings up the Excel 'Find and Replace' dialog box), then
    >> enter the minus character in the 'Find what' box, then
    >> click the [Replace All] button

    zeddy

  13. The Following 2 Users Say Thank You to zeddy For This Useful Post:

    cam (2014-11-13),MikeRansom (2014-11-13)

Posting Permissions

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