Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    11

    Unanswered: dynamic ranges in a loop

    Hi there.

    I want to use a loop in order to write into different, consecutive ranges. Therefore, i need to include the counting variable i in the range definition. The expression below would be inside a loop:

    Range(R[1]C[1+i]:R[2]C[1+i]).Select

    Now the syntax is wrong... How SHOULD it be? Can I use the form

    Range("A1:B1").Select

    to do the same thing?

    Range("A1"&i:"B1"&i).Select

    doesnt work....

    Cheers!

    Fabalicious

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067

    Re: dynamic ranges in a loop

    Hi

    You could use

    Range("A" & i & ":B" & i).Select

    with I being the row that you require

    HTH

    David

  3. #3
    Join Date
    Apr 2004
    Location
    Poland
    Posts
    6

    Re: dynamic ranges in a loop

    Originally posted by fabalicious
    Hi there.

    I want to use a loop in order to write into different, consecutive ranges. Therefore, i need to include the counting variable i in the range definition. The expression below would be inside a loop:

    Range(R[1]C[1+i]:R[2]C[1+i]).Select

    Now the syntax is wrong... How SHOULD it be? Can I use the form

    Range("A1:B1").Select

    to do the same thing?

    Range("A1"&i:"B1"&i).Select

    doesnt work....

    Cheers!

    Fabalicious

  4. #4
    Join Date
    Apr 2004
    Location
    Poland
    Posts
    6

    Re: dynamic ranges in a loop

    Try this:

    Range(Cells(variable, variable), Cells(variable, variable)).Select

  5. #5
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    This may not apply for Fabalicious, but someone might find Areas useful.

    Sub test()
    Dim i As Integer

    'The following statement just names a multi-area range.
    'It's the same as selecting A1:A10 and then CTRL+selecting B1:B10, C1:C10, D110
    'and then Insert, Name, Define myRange
    ActiveWorkbook.Names.Add Name:="myRange", RefersToR1C1:= _
    "=Sheet1!R1C1:R10C1,Sheet1!R1C2:R10C2,Sheet1!R1C3: R10C3,Sheet1!R1C4:R10C4"

    'This loops through each area and then each cell
    For Each area In ActiveWorkbook.Names("myRange").RefersToRange.Area s
    For Each cell In area
    cell.Value = i
    i = i + 1
    Next cell
    Next area
    End Sub
    Last edited by actuary; 04-21-04 at 18:26.

Posting Permissions

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