As you can see on another thread, I had some problems making some cells
being cut down to another row.
(thread:
http://groups.google.com/group/micro...ecc6f058e0f01e)

Tom Ogilvy did a pretty nice job and made a script that did everything
I needed.

But now I need something else. I want to break more than one line,
like:

P|00000|ICMS|IPI|T|123|999|T|123|456
P|00001|ABCD|ASD|T|456|666|T|888|4456
P|00002|LINK|LUNK|T|789|333 |T|321|3214

Would become:

P|00000|ICMS|IPI
T|123|999
T|123|456
P|00001|ABCD|ASD
T|456|666
T|888|4456
P|00002|LINK|LUNK
T|789|333
T|321|3214

A proposal would be that every cell with just a "T" would be triggered
a new row.
Remembering that each "|" means a cell divider.

Thanks in advance specially to Tom Ogilvy, which did a great job.

Can you just break it at column E and column H?

Option Explicit
Sub DDDD2()
Dim i As Long
Dim lastrow As Long
Dim rng As Range
lastrow = Cells(Rows.Count, 1 _
).End(xlUp).Row
For i = lastrow + 1 To 2 Step -1
Set rng = Cells(i - 1, 1)
Rows(i).Resize(2).Insert
Cells(i - 1, 5).Resize(1, 3).Copy Cells(i, 1)
Cells(i - 1, 8).Resize(1, 3).Copy Cells(i + 1, 1)
Cells(i - 1, 5).Resize(1, 6).ClearContents
Next
End Sub

Lunks wrote:

As you can see on another thread, I had some problems making some cells
being cut down to another row.
(thread:
http://groups.google.com/group/micro...ecc6f058e0f01e)

Tom Ogilvy did a pretty nice job and made a script that did everything
I needed.

But now I need something else. I want to break more than one line,
like:

P|00000|ICMS|IPI|T|123|999|T|123|456
P|00001|ABCD|ASD|T|456|666|T|888|4456
P|00002|LINK|LUNK|T|789|333 |T|321|3214

Would become:

P|00000|ICMS|IPI
T|123|999
T|123|456
P|00001|ABCD|ASD
T|456|666
T|888|4456
P|00002|LINK|LUNK
T|789|333
T|321|3214

A proposal would be that every cell with just a "T" would be triggered
a new row.
Remembering that each "|" means a cell divider.

Thanks in advance specially to Tom Ogilvy, which did a great job.

--

Dave Peterson

I can't break on an exact column because it won't always be one or two
new rows. It'll vary on each line.
This is for using data from my old db (which was in DBF) to a new one
in SQL. It has an import layout, which has to follow this template.
As I get this data from Access, I get all of them in a single line.

If I have one product which costs 10 pounds and has a 5% vat (in Brazil
it's not vat, but some other taxes), I'd have to make something like
this:

P|Product Name|Product Code|10.00
T|VAT|5

On some products I won't have any taxes, on some I'll have more than
one, and this would be interpreted as long as it has a "T" below any
"P" lines. Note that "P" means we started to show info about a product,
and until another new row starting with a "P", It'll be info from the
same product.

So, a better method would be insert a new row if there's a single "T"
and, until another single "T" shows up, it'll all be on the same row.

Maybe...

Option Explicit
Sub DDDD2()
Dim iRow As Long
Dim LastRow As Long
Dim NumberOfTs As Long
Dim iCol As Long
Dim oRow As Long
Dim oCol As Long
Dim FoundFirstTCol As Long

With ActiveSheet
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For iRow = LastRow To 2 Step -1
FoundFirstTCol = 0
NumberOfTs = Application.CountIf(.Rows(iRow) _
.Resize(1, .Columns.Count - 1).Offset(0, 1),
"T")
If NumberOfTs 0 Then
.Rows(iRow + 1).Resize(NumberOfTs).Insert
oRow = iRow
oCol = 1
For iCol = 2 To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
If UCase(.Cells(iRow, iCol).Value) = "T" Then
If FoundFirstTCol = 0 Then
FoundFirstTCol = iCol
End If
oRow = oRow + 1
oCol = 1
Else
oCol = oCol + 1
End If
If FoundFirstTCol < 0 Then
.Cells(oRow, oCol).Value = .Cells(iRow, iCol).Value
End If
Next iCol
End If
If FoundFirstTCol 0 Then
.Range(.Cells(iRow, FoundFirstTCol), _
.Cells(iRow, .Columns.Count)).ClearContents
End If
Next iRow
End With
End Sub

Lunks wrote:

I can't break on an exact column because it won't always be one or two
new rows. It'll vary on each line.
This is for using data from my old db (which was in DBF) to a new one
in SQL. It has an import layout, which has to follow this template.
As I get this data from Access, I get all of them in a single line.

If I have one product which costs 10 pounds and has a 5% vat (in Brazil
it's not vat, but some other taxes), I'd have to make something like
this:

P|Product Name|Product Code|10.00
T|VAT|5

On some products I won't have any taxes, on some I'll have more than
one, and this would be interpreted as long as it has a "T" below any
"P" lines. Note that "P" means we started to show info about a product,
and until another new row starting with a "P", It'll be info from the
same product.

So, a better method would be insert a new row if there's a single "T"
and, until another single "T" shows up, it'll all be on the same row.

--

Dave Peterson

I get a syntax error at:

NumberOfTs = Application.CountIf(.Rows(iRow) _
.Resize(1, .Columns.Count - 1).Offset(0,
1),
"T")

Sorry, the line was too long:

NumberOfTs = Application.CountIf(.Rows(iRow) _
.Resize(1, .Columns.Count - 1).Offset(0, 1), "T")



Lunks wrote:

I get a syntax error at:

NumberOfTs = Application.CountIf(.Rows(iRow) _
.Resize(1, .Columns.Count - 1).Offset(0,
1),
"T")

--

Dave Peterson