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, Island hopping somewhere right now
    Posts
    325
    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 
    
    
    Formatting tags added by mark007
    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
    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 
    
    
    Formatting tags added by mark007
    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,137
    Location
    Dim x: x = "7:" 
    Dim y: y = 50 
     
    oArray = Array("K" & x & "K" & y, "L" & x & "L" & y) 
    
    
    Formatting tags added by mark007
    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) 
    
    
    Formatting tags added by mark007
    If I only a two column Range like that
    oRange <=> oArray 
    Set oRange = Range("K" & x & ":L" & y) 
    
    
    Formatting tags added by mark007


    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, Island hopping somewhere right now
    Posts
    325
    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 
    
    
    Formatting tags added by mark007
    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, Island hopping somewhere right now
    Posts
    325
    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
    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 
    
    
    Formatting tags added by mark007
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.
    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
    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 
    
    
    Formatting tags added by mark007

  8. #8
    VBAX Mentor
    Joined
    Feb 2016
    Location
    I have lived in many places, I love to Travel, Island hopping somewhere right now
    Posts
    325
    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
  •