Hello,

I looked in the forum but could not find a good answer for my problem. I
have a sheet with a few hundred lines (download from SAP). The sheet has the
following format:

A B
1 123456
321345
text
2 213453
894352
another text line
3 723467
831487
and another text line!
etc etc

I would like to have a macro that moves the cell that contains the text one
cell to the right and one cell up!

With best regards

Is this what you want

Public Sub ProcessData()
Dim LastRow As Long
Dim NumBreaks As Long
Dim IdxBreak As Long
Dim LastPos As Long
Dim i As Long
Dim j As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For i = 1 To LastRow

NumBreaks = Len(.Cells(i, "A").Value) - Len(Replace(.Cells(i,
"A").Value, vbLf, ""))
IdxBreak = NumBreaks
LastPos = Len(.Cells(i, "A").Value)
For j = LastPos To 1 Step -1

If Mid$(.Cells(i, "A").Value, j, 1) = vbLf Then

.Cells(i, IdxBreak + 1).Value = Mid$(.Cells(i,
"A").Value, j + 1, LastPos - j)
LastPos = j - 1
IdxBreak = IdxBreak - 1
End If
Next j
.Cells(i, "A").Value = Left$(.Cells(i, "A").Value, LastPos)
Next i
End With

End Sub

--
__________________________________
HTH

Bob

"TooN" wrote in message
...
Hello,

I looked in the forum but could not find a good answer for my problem. I
have a sheet with a few hundred lines (download from SAP). The sheet has
the
following format:

A B
1 123456
321345
text
2 213453
894352
another text line
3 723467
831487
and another text line!
etc etc

I would like to have a macro that moves the cell that contains the text
one
cell to the right and one cell up!

With best regards

Hello Bob,

thanks for the quick response. I added the macro into the workbook but there
were no results after running it. Maybe i have to correct myself. The cells
with the text are in column C and i want to move them to column D (one to the
right and one up). So if there is a text line in cel C10 i want to move it to
D9.

Columns:
A B(empty) C D
HE612421100 12473265
12473265
REMOVING DEVICE
HE612421100 12510365
12510365
User Manual
HE612421100 12510465
12510465
Predicted Performance Map Overall LP
HE612421100 12510665
12510665
Nozzle Allowable Loading LP
HE612421100 12510865
12510865
Compressor Data Sheets
HE612421100 12510965
12510965
etc


thanks in advance!

"Bob Phillips" wrote:

Is this what you want

Public Sub ProcessData()
Dim LastRow As Long
Dim NumBreaks As Long
Dim IdxBreak As Long
Dim LastPos As Long
Dim i As Long
Dim j As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For i = 1 To LastRow

NumBreaks = Len(.Cells(i, "A").Value) - Len(Replace(.Cells(i,
"A").Value, vbLf, ""))
IdxBreak = NumBreaks
LastPos = Len(.Cells(i, "A").Value)
For j = LastPos To 1 Step -1

If Mid$(.Cells(i, "A").Value, j, 1) = vbLf Then

.Cells(i, IdxBreak + 1).Value = Mid$(.Cells(i,
"A").Value, j + 1, LastPos - j)
LastPos = j - 1
IdxBreak = IdxBreak - 1
End If
Next j
.Cells(i, "A").Value = Left$(.Cells(i, "A").Value, LastPos)
Next i
End With

End Sub

--
__________________________________
HTH

Bob

"TooN" wrote in message
...
Hello,

I looked in the forum but could not find a good answer for my problem. I
have a sheet with a few hundred lines (download from SAP). The sheet has
the
following format:

A B
1 123456
321345
text
2 213453
894352
another text line
3 723467
831487
and another text line!
etc etc

I would like to have a macro that moves the cell that contains the text
one
cell to the right and one cell up!

With best regards