Dim Counter As Integer
Dim Maxhouse As Integer
Dim FindHouse As Range
Dim RangeVar As Range
Dim HousesRange As Range
For Counter = 1 To MaxHouse
ActiveSheet.Cells(16, 2 + Counter).Select
House = ActiveCell
With Sheets("Sheet1").Range("C:KP")
Set FindHouse = Cells.Find(What:=House, _
After:=Cells(17, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not FindHouse Is Nothing Then
If Counter = 1 Then
Set HousesRange = FindHouse
Else
Set RangeVar = FindHouse
Set HousesRange = Union(HousesRange, RangeVar)
End If
End If
End With
Next Counter
For Each RCell In HousesRange.Cells
Application.Goto RCell, True
Next RCell**
Now my problem is with the for loop which traverses through the named range 'HousesRange'
So lets say that HousesRange contains [2,5,9,10].
Here HousesRange is a subset of the row [1,2,3,4,5,6,7,8,9,10] in my Sheet
And lets assume that HousesRange was established through the order of [9,10,5,2] (through the 1st for loop with the union).
Now as I traverse through HousesRange with just rCells (the second for loop), it takes me to 9, 10, 5 then 2.
But I want it to take me to 2, 5, 9 then 10
Can some body shed some light to this?
I had always thought that named ranges are ALWAYS traversed through left to right and then top to bottom.
Thank you so much in advance
Ok this is the long way round, but it should work:
Instead of using
Union
build your list of found houses in a dictionary object. Then sort the ranges usingBubblesort HouseRangeDic
You should finally be able to use it in the right order: