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

[tropics123]

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
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Loan Dist[/TD]
[TD]Int[/TD]
[TD]Prin[/TD]
[TD]Transfers[/TD]
[TD]Loan Dist[/TD]
[TD]Int[/TD]
[TD]Prin[/TD]
[TD]Gain/Loss[/TD]
[/TR]
[TR]
[TD]-450[/TD]
[TD]880[/TD]
[TD]20,000[/TD]
[TD][/TD]
[TD]-8,900[/TD]
[TD][/TD]
[TD]560,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-250[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]-5[/TD]
[TD]65[/TD]
[TD]220,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20[/TD]
[TD]1,500[/TD]
[TD][/TD]
[TD]-180[/TD]
[TD]86[/TD]
[TD]86,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-100[/TD]
[TD]6000[/TD]
[TD]100,000[/TD]
[TD][/TD]
[TD]-120[/TD]
[TD]123[/TD]
[TD]150,000[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Desired outcome
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Loan Dist[/TD]
[TD]Int[/TD]
[TD]Prin[/TD]
[TD]Transfers[/TD]
[TD]Loan Dist[/TD]
[TD]Int[/TD]
[TD]Prin[/TD]
[TD]Gain/Loss[/TD]
[/TR]
[TR]
[TD]450[/TD]
[TD][/TD]
[TD]-20,880[/TD]
[TD]
[/TD]
[TD]8,900[/TD]
[TD]
[/TD]
[TD]-560,000[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]250[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]-220,065[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD][/TD]
[TD]-1,520[/TD]
[TD]
[/TD]
[TD]180
[/TD]
[TD]
[/TD]
[TD]-86,086[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]
[/TD]
[TD]-106,000[/TD]
[TD]
[/TD]
[TD]120[/TD]
[TD]
[/TD]
[TD]-150,123[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

 

[igold]

How about this...

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

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]

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]

My bad, I blew by the Loan Dist requirement. I will re-post with changes.

 

[igold]

Is this better...

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.