Set few range variables at once

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Is there a way to do the below implicitly in a loop or as array? Thank you!


Dim States As Range
Dim Alaska As Range, Alabama As Range, Arizona As Range 'etc

Set States = Range("a2:a51")

With States
Set Alaska = .Cells.Find("Alaska", lookat:=xlWhole, MatchCase:=False)
Set Alabama = .Cells.Find("Alabama", lookat:=xlWhole, MatchCase:=False)
'...
'..
'etc

End With
 
Greg,

I would rethink my approach here. You have 50 separate variables, each of which require celaration and assignment: this is not
really "loopable" as it stands.

A better approach might be to create a small function to return a range based on a State name, or to use a dictionary object to
associate names and ranges.

All depends to some extent (performance-wise) on what you're going to do with those 50 variables...

Tim
 
The variables will be used to identify rows with data for the particular
state.

Thanks for your comments, Tim. Yes, I do feel the same way, need to come up
with some go around thinking.

--
______
Regards,
Greg


Tim Williams said:
Greg,

I would rethink my approach here. You have 50 separate variables, each of which require celaration and assignment: this is not
really "loopable" as it stands.

A better approach might be to create a small function to return a range based on a State name, or to use a dictionary object to
associate names and ranges.

All depends to some extent (performance-wise) on what you're going to do with those 50 variables...

Tim
Click to expand...
 
Maybe you could use a collection, so you could refer to the row by using the
state name:

Option Explicit
Sub testme01()

Dim myStates As Variant
Dim myStateCells As Collection
Dim FoundCell As Range
Dim iCtr As Long
Dim myStateName As String

Set myStateCells = New Collection

myStates = Array("alaska", "alabama", "illinois", "Wyoming")
'add in all 50 + DC???

With States
For iCtr = LBound(myStates) To UBound(myStates)
Set FoundCell = .Cells.Find(what:=myStates(iCtr), _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
myStateCells.Add FoundCell, myStates(iCtr)
Next iCtr
End With

myStateName = "alaska"
If IsError(Application.Match(myStateName, myStates, 0)) Then
MsgBox "Misspelled State Name!"
Else
'find alaska cell
If myStateCells(myStateName) Is Nothing Then
MsgBox "Not found"
Else
MsgBox myStateCells(myStateName).Row
End If
End If

End Sub
 
A couple of ideas involving collections, marked A & B

Sub test()
Dim i As Long
Dim rng As Range
Dim ColRanges As Collection 'A
Dim colNames As Collection 'B
Dim arrStates()

arrStates = Array("Alaska", "Alabama", "Arizona")

ReDim ar(1 To UBound(arrStates) + 1) As Range

i = 0
Set ColRanges = New Collection ' A
Set colNames = New Collection ' B

For Each zone In arrStates
i = i + 1
ColRanges.Add Cells(i, 1), zone 'A

Set ar(i) = Cells(i, 2) 'B
colNames.Add i, zone 'B
Next

i = 0
For Each r In ColRanges 'A
i = i + 1
Debug.Print r.Address
Next

Debug.Print "Arizona", ColRanges("Arizona").Address 'A

Debug.Print ar(colNames("Arizona")).Address 'B

End Sub

For your Set to found cells (subject error handing)

method A
ColRanges.Add foundRange, "myState"

method B
first populate the colNames collection
Set ar(colNames("myState")) = foundRange

Regards,
Peter T
 
Back
Top