Add two columns together, paste result over one of the columns and change to negative sign
Results 1 to 5 of 5

Thread: Add two columns together, paste result over one of the columns and change to negative sign
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2016
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Add two columns together, paste result over one of the columns and change to negative sign

    Hi, I would greatly appreciate help on this as I'm stuck and macro doesn't work Here's what I'm trying to do:

    1) Find all headers with the names "Int" and "Prin" and sum the two columns together and put the new result in column "Prin". It would override the previous numbers in "Prin"
    2) Delete all numbers in the "Int" columns
    3) In columns "Loan Dist" and "Prin", change the numbers to opposite signs (this has to be the last step so it doesn't effect the prin amount in step 1)

    My macro is below. Thanks much!!

    Example of original data
    Loan Dist Int Prin Transfers Loan Dist Int Prin Gain/Loss
    -450 880 20,000 -8,900 560,000
    -250 -5 65 220,000
    20 1,500 -180 86 86,000
    -100 6000 100,000 -120 123 150,000

    Desired outcome
    Loan Dist Int Prin Transfers Loan Dist Int Prin Gain/Loss
    450 -20,880
    8,900
    -560,000
    250


    5 -220,065

    -1,520
    180

    -86,086
    100
    -106,000
    120
    -150,123

    Macro:

    Sheets("Sheet3").Select
    Dim A As Range
    Dim i As Long


    Set A = Rows(1).Find(what:="Int", LookIn:=xlValues, lookat:=xlPart)
    Set A = Rows(1).Find(what:="Prin", LookIn:=xlValues, lookat:=xlPart).FormulaR1C1 = "Sum(RC1, RC2)"


    Do
    Set A = Rows(1).Find(what:="Loan Dist", LookIn:=xlValues, lookat:=xlPart)
    Set A = Rows(1).Find(what:="Prin", LookIn:=xlValues, lookat:=xlPart)
    If A Is Nothing Then Exit Do
    For i = LBound(A) To UBound(A)
    A(i, 1) = -A(i, 1)
    Next i
    Loop
    End Sub
    Last edited by tropics123; Jul 2nd, 2019 at 11:27 AM.

  2. #2
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,325
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Add two columns together, paste result over one of the columns and change to negative sign

    How about this...

    Please test on a backup copy of your sheet as this code will delete data.

    Code:
    Sub PrinInt()
    
    
        Dim lRow As Range, fHdr As Range
        Dim i As Long, x As Long
        Dim inte
        
        Set lRow = Cells(Cells.Find(what:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row, _
            Cells.Find(what:="*", SearchOrder:=xlByColumns, _
            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column)
        Set fHdr = ActiveSheet.Range("1:" & lRow.Column).Find(what:="Int")
        
        With ActiveSheet
            For x = 1 To lRow.Column
                If Cells(1, x).Value = "Int" Then
                    inte = Range(Cells(2, x), Cells(lRow.Row, x + 1))
                        For i = LBound(inte) To UBound(inte)
                            If Not inte(i, 2) = Empty Then inte(i, 2) = (inte(i, 1) + inte(i, 2)) * -1
                            inte(i, 1) = Empty
                        Next
                    .Cells(2, x).Resize(UBound(inte, 1), 2) = inte
                End If
            Next
        End With
    
    
    End Sub
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  3. #3
    New Member
    Join Date
    May 2016
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add two columns together, paste result over one of the columns and change to negative sign

    Hi, the column "Loan Dist" might be in different columns depending on the data received. Could you help me with that issue too? Can the macro look for the name "Loan Dist" instead?

  4. #4
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,325
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Add two columns together, paste result over one of the columns and change to negative sign

    My bad, I blew by the Loan Dist requirement. I will re-post with changes.
    Last edited by igold; Jul 3rd, 2019 at 07:47 AM.
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  5. #5
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,325
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Add two columns together, paste result over one of the columns and change to negative sign

    Is this better...

    Code:
    Sub PrinInt()
    
    
        Dim lRow As Range, fHdr As Range
        Dim i As Long, x As Long, d As Long
        Dim inte, Ld
        
        Set lRow = Cells(Cells.Find(what:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row, _
            Cells.Find(what:="*", SearchOrder:=xlByColumns, _
            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column)
        Set fHdr = ActiveSheet.Range("1:" & lRow.Column).Find(what:="Int")
        
        With ActiveSheet
            For x = 1 To lRow.Column
                If Cells(1, x).Value = "Int" Then
                    inte = Range(Cells(2, x), Cells(lRow.Row, x + 1))
                        For i = LBound(inte) To UBound(inte)
                            If Not inte(i, 2) = Empty Then inte(i, 2) = (inte(i, 1) + inte(i, 2)) * -1
                            inte(i, 1) = Empty
                        Next
                    .Cells(2, x).Resize(UBound(inte, 1), 2) = inte
                End If
                If Cells(1, x).Value = "Loan Dist" Then
                    Ld = Range(Cells(2, x), Cells(lRow.Row, x))
                        For d = LBound(Ld) To UBound(Ld)
                            If Not Ld(d, 1) = Empty Then Ld(d, 1) = Ld(d, 1) * -1
                        Next
                    .Cells(2, x).Resize(UBound(Ld, 1)) = Ld
                End If
            Next
        End With
    
    
    End Sub
    Sorry about my confusion.
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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