Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Location
    Bethlehem, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Looping (Excel XP)

    I have a report that I import on a weekly basis. I need to do some formatting to this report and would like to create a macro to do this. I have create a macro that check the number of characters in the previous cell

    Sub NoOfCharacter()
    ActiveCell.FormulaR1C1 = "=LEN(RC[-1])"
    ActiveCell.Offset(1, 0).Range("A1").Select
    End Sub

    OK not hard.

    My question is I want to apply this code to all the cell in this column until I get to the bottom of the row.

    I imagine it would be something like(psuedocode)
    Do until I reach the bottom of the column
    ...
    loop
    what I can't figure out is what the code to do this.

    Once I figure this out I will be able to apply this to another macro that will do the formatting

    Note: This report will have different row counts every week.

    Thanks in advance

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looping (Excel XP)

    You don't need a loop, you can just write that forumla into the whole column at once, like so:-

    Range(ActiveCell,Cells(65536,ActiveCell.Column-1).End(xlUp).Offset(0,1)).FormulaR1C1 = "=LEN(RC[-1])"

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Location
    Bethlehem, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looping (Excel XP)

    Adam,

    Thanks for the reply. It work perfectly

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looping (Excel XP)

    You're welcome! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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