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
1	Account	Amount	Currency
2	AB23 JFK5 EUR	50000	EUR
3	CD55 OK99 USD	20000	USD
4	ED55 QA85 GBP	80000	GBP

into :

	A	B	C
1	Account	Amount	Currency
2	AB23 JFK5 EUR	50000	EUR
3	JFK5 AB23 EUR	-50000	EUR
4	CD55 OK99 USD	20000	USD
5	OK99 CD55 USD	-20000	USD
6	ED55 QA85 GBP	80000	GBP
7	QA85 ED55 GBP	-80000	GBP

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