How to invert and copy data from a cell?

[L2VB]

Hello,

I seek your help because I would like to automate a tedious task on one of my excel databases.
I am still a beginner with VBA, so I don't really know how to do what I want, this is why I'm asking you guys for help.
In my database I have information about loans, and what I want to do is copy every line while inverting the account number and changing the sign of the amount.


For example I have:

Account Amount Currency
AB23 JFK5 EUR 50 000 Euro

I would want to have:

Account Amount Currency
AB23 JFK5 EUR 50 000 Euro
JFK5 AB23 EUR -50 000 Euro

Basically I want to add a line with the exact same information for everything, except for the account number which is inverted (from AB23 JFK5 EUR to JFK5 AB23 EUR) and the amount which changes sign (from 50 000 to - 50 000).

Thank you so much, I really appreciate your help.

[jolivanes]

So you have three headers (Account, Amount and Currency). This should be in Cells A1, B1 and C1.
So what is in the Cells in the next Row?
I just assumed that it is not all in one Cell.

[KOKOSEK]

From:



A
B
C

1AccountAmountCurrency



2AB23 JFK5 EUR
50000EUR



3CD55 OK99 USD
20000USD



4ED55 QA85 GBP
80000GBP



into :


A
B
C

1AccountAmountCurrency



2AB23 JFK5 EUR
50000EUR



3JFK5 AB23 EUR
-50000EUR



4CD55 OK99 USD
20000USD



5OK99 CD55 USD
-20000USD



6ED55 QA85 GBP
80000GBP



7QA85 ED55 GBP
-80000GBP
Sheet: Sheet1


Try this:


Sub ReArrangeAccount()
Dim i As Long
Dim Account() As String
For i = 2 To 10 'set accordnigly to your needs or can be set dynamically
If Cells(i, "A").Value <> "" Then
Rows(i + 1).EntireRow.Insert
Account() = Split(Cells(i, "A").Value, " ")
Cells(i + 1, "A").Value = Account(1) & " " & Account(0) & " " & Account(2)
Cells(i + 1, "A").Offset(0, 1).Value = Cells(i, "A").Offset(0, 1).Value * -1
Cells(i + 1, "A").Offset(0, 2).Value = Cells(i, "A").Offset(0, 2).Value
i = i + 1
End If
Next i
End Sub

[jolivanes]

If it is all in a single cell, this should work.

Sub Single_Cell_Code()
Dim i As Long, a
Application.ScreenUpdating = False
For i = 3 To 2 * Cells(Rows.Count, 1).End(xlUp).Row Step 2
Cells(i, 1).Insert
a = Split(Cells(i, 1).Offset(-1), " ")
Cells(i, 1).Value = a(1) & " " & a(0) & " " & a(2) & " -" & a(3) & " " & a(4) & " " & a(5)
Next i
Application.ScreenUpdating = False
End Sub


If it is like in KOKOSEK's example, this should work.

Sub Three_Cells_Code()
Dim i As Long, a
Application.ScreenUpdating = False
For i = 3 To 2 * Cells(Rows.Count, 1).End(xlUp).Row Step 2
Cells(i, 1).Resize(, 3).Insert
a = Split(Cells(i, 1).Offset(-1), " ")
Cells(i, 1).Value = a(1) & " " & a(0) & " " & a(2)
Cells(i, 1).Offset(0, 1).Value = "-" & Cells(i, 1).Offset(-1, 1).Value
Cells(i, 1).Offset(0, 2).Value = Cells(i, 1).Offset(-1, 2).Value
Next i
Application.ScreenUpdating = False
End Sub