Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    General: For Loops (2000)

    Hi, is this possible?

    For Each Cell in Columns("K")
    msgbox("Hello")
    Next

    I basically want to go down a column and do something with each cell. I know how to do this using offset and looping, but i was wondering if it can be done with a loop such as this and avoid "offset".

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: General: For Loops (2000)

    Kevin,

    Your loop should work fine, but dont try it unless you want click on OK 65,536 times. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    However if you replace the MsgBox function with say cell.value = X, you should find all cells in column K will conatin whetever X is.

    Andrew

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: General: For Loops (2000)

    If you want to go down each cell in the column, then you should use the code below. Your code will only loop once with Cell set to the entire column.

    <pre>Dim Cell As Range
    For Each Cell In Range("K:K")
    MsgBox (Cell.Address)
    Next
    </pre>

    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: General: For Loops (2000)

    A picky point first: the way your code is constructed, the variable "Cell" will be assigned the range of all cells in column K, and will only loop once.

    To loop through each cell in the range, you should add .Cell to teh end of the For Each line, as follows:

    <pre>For Each C In Columns("K").Cells
    MsgBox C.Address
    Next C
    </pre>


    Now, as someone else pointed out, this is OK, only if you want to click OK 65,536 times. Therefore, it is good to figure out a smaller range through which to loop, maybe like so:

    <pre> Dim C As Range, R As Range

    Set R = Intersect(ActiveSheet.UsedRange, Columns("K"))

    For Each C In R.Cells
    MsgBox C.Address
    Next C

    </pre>


Posting Permissions

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