Results 1 to 4 of 4

For Each in a Range

This is a discussion on For Each in a Range within the Excel Questions forums, part of the Question Forums category; Can I use a For Each to loop thru a named range, and say change the values? I know I ...

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    110

    Default For Each in a Range

    Can I use a For Each to loop thru a named range, and say change the values? I know I can do it with a for next like the following:

    Dim i As Integer

    For i = 1 To Range("MyRange").Cells.Count
    Range("Myrange").Cells(i).Value = Range("Myrange").Cells(i).Value * 3
    Next i

    Thanks.
    Mike

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824

    Default Re: For Each in a Range

    Yes, Mike.

    This will effectively replace your code:

    Code:
    Dim c As Range
    
    For Each c In [MyRange]
        c = c * 3
    Next
    Tom

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    110

    Default Re: For Each in a Range

    Tom,

    When I try that I get the error message Expected Function or Variable.
    and MyRange is selected.

    Dim c As Range

    For Each c In [MyRange]
    c = c * 3
    Next

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824

    Default Re: For Each in a Range

    Well,
    I just tested it out.
    I'm assuming that you have a range named "MyRange"?

    if so, maybe this is better code...

    Code:
    Dim c As Range
    
    For Each c In Range("MyRange")
        c.Value = c.Value * 3
    Next
    Tom

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com