Array Prefix & Suffix Methods

[dj44]

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?

[Paul_Hossler]

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

[SamT]

  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

[dj44]

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

[dj44]

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

[Paul_Hossler]

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

[snb]

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

[dj44]

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