Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Format WrapText in a loop (VBA Excel 2003)

    Hi,

    Im currently writing a vba excel macro to import data from a text file which all works fine. Im currently stuck on formating data in column B, because there is so much data in the cell it needs to be wrap text, but I cant seem to format the whole column as part of the loop when bringing the data in, or before/after the data is entered.

    Heres my coding, which basically takes all the data for one person and puts it onto a separate worksheet labelling that worksheet with their name.

    For iLoop = 1 To 100
    If StrFe(iLoop) <> "" Then
    Selection.AutoFilter Field:=1, Criteria1:=StrFe(iLoop)
    Cells.Select
    Selection.Copy
    Sheets.Add
    ActiveSheet.Select
    ActiveSheet.Name = StrFe(iLoop)
    Range("A1").Select
    ActiveSheet.Paste
    Cells.EntireColumn.AutoFit
    Columns("B:B").Select
    Selection.WrapText = True
    Range("E12").Select
    Sheets("Sheet1").Select
    Range("D21").Select
    Application.CutCopyMode = False
    End If
    Next

    Any ideas <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

    Cheers
    Lee

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Format WrapText in a loop (VBA Excel 2003)

    What exactly is the problem? The lines

    Columns("B:B").Select
    Selection.WrapText = True

    which could be combined into

    Columns("B:B").WrapText = True

    should turn on Wrap Text. If you select some cells in column B in one of the target worksheets, then inspect the Alignment tab of Format | Cells, is Wrap Text off?

  3. #3
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Format WrapText in a loop (VBA Excel 2003)

    Hi Hans,

    Ive checked cells in column B in the target worksheets and they all have text wrap turned on.

    Could it be a refresh issue? The data in column B is has commas in it, could those commas be confusing it?

    Column B also has a title header such as 'Property' with a filter attached. Does this column have to have sufficient data in it for the text wrap to start?

    Regards.
    Lee

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Format WrapText in a loop (VBA Excel 2003)

    The filter and the commas shouldn't matter. See if adding a line

    Rows.AutoFit

    helps. BTW, do you have merged cells in column B? That could prevent Excel from autofitting row heights.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Format WrapText in a loop (VBA Excel 2003)

    Rows.AutoFit - Are my two favorite words for today.....It works !!! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    Are you able to explain how that managed to get the test wrap to behave, if possible, so I can understand it better?

    Lee

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Format WrapText in a loop (VBA Excel 2003)

    For text wrappimg to be effective, the height of rows must be adjusted - by default, rows in Excel are only one line of text high, so there is nothing to wrap. Excel will often adjust the row heights automatically when Text Wrap is turned on, but if that doesn't work, Rows.AutoFit forces Excel to adjust the row heights.

  7. #7
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Format WrapText in a loop (VBA Excel 2003)

    Thanks Hans.

    By the way can I amend this script line for the document to be printed out in Landscape - ActiveWindow.SelectedSheets.PrintOut, Copies:=1, Collate:=True ?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Format WrapText in a loop (VBA Excel 2003)

    PrintOut doesn't let you specify the page orientation. You must insert a separate instruction before the PrintOut line:

    ActiveSheet.PageSetup.Orientation = xlLandscape

  9. #9
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Format WrapText in a loop (VBA Excel 2003)

    Great.

    Thanks for all your help.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Lee

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •