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

tropics123

Board Regular
Joined
May 11, 2016
Messages
85
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 DistIntPrinTransfersLoan DistIntPrinGain/Loss
-45088020,000-8,900560,000
-250-565220,000
201,500-1808686,000
-1006000100,000-120123150,000

<tbody>
</tbody>

Desired outcome
Loan DistIntPrinTransfersLoan DistIntPrinGain/Loss
450-20,880
8,900
-560,000
250


5-220,065

-1,520
180

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

<tbody>
</tbody>

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:

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,493
Office Version
365, 2010
Platform
Windows
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
 

tropics123

Board Regular
Joined
May 11, 2016
Messages
85
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?
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,493
Office Version
365, 2010
Platform
Windows
My bad, I blew by the Loan Dist requirement. I will re-post with changes.
 
Last edited:

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,493
Office Version
365, 2010
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,302
Messages
5,486,053
Members
407,529
Latest member
netojose

This Week's Hot Topics

Top