Text manipulation with Visual Basic

[jip]

Platform: Windows 2000/Excel 2000


Is there anybody out here who is able to help me solving following
Excel-case, I am a novice using VB therefore I hope you are a little passion
with me:

I have an Excel-file consists of approximately 56000 rows and 10 columns,
one of these columns (lets say column E) contain a description of characters
from 0 to 80.

I need Visual-basic to go through each cell in column E and divide the text
into two "lines" first "line" can maximum consist of 40 characters the
second "line" has no maximum. My problem is that the text often will be
divided in middle of a word that I do not want.
Therefore I imagine the program first check on character 40 if it is a
"space" or not. If it is a space nothing has to be done opposite the program
must go back through the text from character 40 until it meet a space.
Afterwards it shall put in "spaces" from the found space up to characters
40.

That is all what I need.

JIP

 

[Tom Ogilvy]

Sub AABB()
Dim rng As Range, cell As Range
Dim sStr As String, sSTr1 As String
Dim i As Long
Set rng = Range(Cells(2, 5), Cells(Rows.Count, 5).End(xlUp))

For Each cell In rng
' remove trim if you don't want leading and
' trailing spaces removed
sStr = Trim(cell.Value)
If Len(sStr) > 40 Then
sSTr1 = Left(sStr, 40)
i = 40
Do While Mid(sSTr1, i, 1) <> " " And i > 0
i = i - 1
Loop
If i < 2 Then
cell.Interior.ColorIndex = 3
Else
sStr = Left(sStr, i - 1) & vbLf & Right(sStr, Len(sStr) - i)
cell.Value = sStr
End If
End If
Next
End Sub

Test on a copy of your workbook/data. You will need to format the column to
wordwrap.

 

[jip]

Hi Tom,
Thank you for the help I now just need to test it. When you mention wordwrap
do you meant by that I have to change the column to text in the excel file
before I do the program manipulation, or...? Sorry, but as I wrote I am
novice using VB.
JIP

 

[Tom Ogilvy]

I would see no reason to do anything but run the macro on a copy of your
workbook.

if you see the text as a single line with a square box shown in the text,
then you need to select the column and do

Format=>Cells=>Alignment tab and select Wrap text. Make the column width
at least 40 characters wide.

this part has nothing to do with VBA.