+ Reply to Thread
Results 1 to 3 of 3

Insert a different named range into VBA loop with each iteration

  1. #1
    Registered User
    Join Date
    08-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Insert a different named range into VBA loop with each iteration

    I'm trying to write an efficient macro that first identifies 13 different named ranges of varying size (not single named cells, of which I also have many), then uses one range at a time for each of 13 iterations of the loop. The repetitive operation is to (1) copy a random draw value to each cell within the selected range, (2) call a separate macro, and (3) change each cells' contents back to the formula it was prior to (1). Then move to the next range and repeat. I'm getting better at declaring variables then using them with each visit to this forum, but I can't find a solution to this specific problem. Here's what I have thus far (and preemptive thanks!):

    Sub blrp()

    Dim MyRange(1 To 3) As Range
    MyRange(1) = Sheets("Parameter Table").Range("blah1")
    MyRange(2) = Sheets("Parameter Table").Range("blah2")
    MyRange(3) = Sheets("Parameter Table").Range("blah3")

    Dim i As Long
    Dim MyRanges As Single
    MyRanges = 3 'decreased from 13 for this post

    For i = 1 To MyRanges
    MyRange(i).Formula = "=VALUE(RC[5])"
    Run blrpdrp
    MyRange(i).Formula = "=IF(*abbreviated)
    Next i

    End Sub

  2. #2
    Valued Forum Contributor
    Join Date
    06-19-2010
    Location
    Holywell, N Wales, UK
    MS-Off Ver
    Excel 2013
    Posts
    470

    Re: Insert a different named range into VBA loop with each iteration

    Hello Dr_Gzus
    Not sure if this is what you require
    If not it may be something to build-on

    Ranges.xlsm
    If this was helpful then please click the small star icon at the bottom left of my post. A little appreciation goes a long way.

  3. #3
    Registered User
    Join Date
    08-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Insert a different named range into VBA loop with each iteration

    I like what you did there. I bumbled my way to a different solution that worked as well, but I'll definitely incorporate your work into my future thinking. Thanks. FYI, my code:

    Sub blrp()

    Dim MyRange(1 To 3) As Range
    Set MyRange(1) = Sheets("Parameter Table").Range("blah1")
    Set MyRange(2) = Sheets("Parameter Table").Range("blah2")
    Set MyRange(3) = Sheets("Parameter Table").Range("blah3")

    Dim i As Long
    Dim MyRanges As Single
    MyRanges = 3 'decreased from 13 for this post

    For i = 1 To MyRanges
    MyRange(i).Formula = "=VALUE(RC[5])"
    blrpdrp
    MyRange(i).Formula = "=IF(*abbreviated)
    DoEvents
    Next i

    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1