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:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
 
Upvote 0
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?
 
Upvote 0
My bad, I blew by the Loan Dist requirement. I will re-post with changes.
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top