Consulting

Results 1 to 8 of 8

Thread: Array Prefix & Suffix Methods

  1. #1
    VBAX Mentor
    Joined
    Feb 2016
    Location
    I have lived in many places, I love to Travel
    Posts
    402
    Location

    Array Prefix & Suffix Methods

    Hello folks,

    a quick one

    in my array I am listing ranges.

    so far i have come up with this but it does look verbose.



       Sub Array_Tasks()
       
        Dim x: x = "7:"
        Dim y: y = 50
        
       
        oArray = Array("K" & x & "K" & y, "L" & x & "L" & y)
        
        ' which would be   "K7:K50"       "L7:L50"
        
       
       End Sub
    is there an eaiser way to make the ranges in an array?
    Cheers for your help

    dj

    'Extreme VBA Newbie in progress - one step at a time - like a tortoise's pace'


  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    5,925
    Location
    Depends on what you're trying to do


    Option Explicit
    Sub Array_Tasks_1()
        Dim aryRanges() As Variant
        Dim x As String
        x = "7:"
        Dim y As String
        y = "50"
         
         
        aryRanges = Array(Range("K" & x & "K" & y), Range("L" & x & "L" & y))
         
        MsgBox aryRanges(0).Address
        MsgBox aryRanges(1).Address
        
         aryRanges(0).Interior.Color = vbGreen
         aryRanges(1).Interior.Color = vbRed
         
    End Sub
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,840
    Location
      Dim x: x = "7:" 
        Dim y: y = 50  
         
        oArray = Array("K" & x & "K" & y, "L" & x & "L" & y)
    Those aren't Ranges, those are Range.Addresses with variables, that's how you do it.

    For an array of Ranges, Pauls method does that in one step.

    I prefer to explicitly Set the Ranges, then assign them to the Array
    Set Range_1 = Range("K" & x & ":K" & y)
    Set Range_2 = Range("L" & x & ":L" & y)
    
    oArray = Array(Range_1, Range_2)
    If I only a two column Range like that
    oRange <=> oArray
    Set oRange = Range("K" & x & ":L" & y)


    I LIKE verbose code
    Please take the time to read the Forum FAQ

  4. #4
    VBAX Mentor
    Joined
    Feb 2016
    Location
    I have lived in many places, I love to Travel
    Posts
    402
    Location
    Thank you Paul

    I was thinking


    Sub Array_Tasks_1()
        Dim aryRanges() As Variant
        Dim x As String
        x = "7:"
        Dim y As String
        y = "50"
         
         
        aryRanges = Array(Range("K" & x & "K" & y), Range("L" & x & "L" & y))
        
        
        
        For i = LBound(aryRanges) To UBound(aryRanges)
        
          aryRanges(i).Interior.Color = vbGreen
      
      
        Next i
        
        
    End Sub
    may be do something to the ranges in a for loop

    I have a lot of ranges to list and the constant number I change and then i have to manually type each one, so i thought an array constant number would help as laid above, so i can change my x and y and not have to type each one
    Cheers for your help

    dj

    'Extreme VBA Newbie in progress - one step at a time - like a tortoise's pace'


  5. #5
    VBAX Mentor
    Joined
    Feb 2016
    Location
    I have lived in many places, I love to Travel
    Posts
    402
    Location
    Thank you Sam,

    I am happy to learn something new every time

    Set Range_1 = Range("K" & x & ":K" & y)
    Set Range_2 = Range("L" & x & ":L" & y)

    oArray = Array(Range_1, Range_2)


    i can do this too, if the verbosity ovewhelms me - we ll they do look like those fireworks about to go off

    Ok let me play about , have a feeling my for loop will moan about the ranges not set up correctly and something about the array not being declared, thinking out loud

    thank you gentlemen, ill be back later

    good evening
    Cheers for your help

    dj

    'Extreme VBA Newbie in progress - one step at a time - like a tortoise's pace'


  6. #6
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    5,925
    Location
    It's still not clear to me what you want to do, but this is one way to have Excel generate range variables for you in a macro

    The collection key is the first cell ('header') in the column, and the range is one row below that one to the end of data in the column



    Option Explicit
    Dim rngCollection As Collection
    
    Sub demo()
        Dim r As Range, c As Range
        
        Set rngCollection = New Collection
        
        For Each r In Range("C4:G4").Cells      '   can be any row
            rngCollection.Add Range(r.Offset(1, 0), r.End(xlDown)), r.Value
        Next
        
        
        rngCollection("First").Interior.Color = vbRed
        
        rngCollection("Last").Interior.Color = vbGreen
        
        MsgBox rngCollection("Street").Count
    
        For Each c In rngCollection("Something").Cells
            MsgBox c.Address & " -- " & c.Value
        Next
    End Sub
    Attached Files Attached Files
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,400
    Sub M_snb() 
      with range("K7:K50")
        sn=array(.0ffset, .offset(,1), .offset(,2))
        sp=array(.address,.offset(,1).address,.offset(,2).address)
      end with     
    End Sub

  8. #8
    VBAX Mentor
    Joined
    Feb 2016
    Location
    I have lived in many places, I love to Travel
    Posts
    402
    Location
    Thank you for your help Paul,

    my foray into Collections has been extremely limited.

    I am still to get the hang of arrays, as many a mishap always happns.

    So Collections are reallly useful for naming ranges on the go , and the column header does that for me.


    I can generate large amounts of ranges without worrying about - the way i was doing it before prefix and suffixing it, that was a long way.

    and i hate to have to type in one by one the ranges, as i change my mind like the wind, and maths is not my wizadry

    well all is good

    Good Sunday Paul and folks

    and thanks again for all the help
    Cheers for your help

    dj

    'Extreme VBA Newbie in progress - one step at a time - like a tortoise's pace'


Posting Permissions

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