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 :ipray:

[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 :grinhalo:

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