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

1. ## 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

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

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```

3. ## 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. ## 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.

5. ## 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```

## User Tag List

#### Posting Permissions

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