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

#### tropics123

##### Board Regular
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

<tbody>
</tbody>

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

<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

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
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
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
My bad, I blew by the Loan Dist requirement. I will re-post with changes.

Last edited:

#### igold

##### Well-known Member
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.

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