MS VBA with loops and unions

Question

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

Is this code complete? Can you add information of `HousesRange.address` result before loop starts. — Kazimierz Jawor, Dec 06 '13 at 05:10
no. This is the partial code. Example: Household contains the cells [1,2,3,4] but it traverses in the order [3, 4, 2, 1] assuming that this is the order in which the union was formed — user3072955, Dec 06 '13 at 05:13
I've checked different options like `Areas`, `For i >> .cells(i)` and nothing is working as you expect. I admit, at the moment I have no idea on how to sort it quickly. — Kazimierz Jawor, Dec 06 '13 at 05:45
Hi KazJaw, I was under the assumption that VBA/Excel ALWAYS traversed through a named range left to right, then top to bottom.. but this is not the case here. Please see my comments below to shreyansp for more information! — user3072955, Dec 06 '13 at 05:50
What's House, a range or a string variable? Not clear in your code. Also, where is MaxHouse assigned a value? — DaveU, Dec 06 '13 at 05:58
House is just an integer (numeric value of cells in the datasheet) MaxHouse is an integer as well. So from a list of houses, I find the house, put it into an union and try to traverse through that union — user3072955, Dec 06 '13 at 05:59
So you're trying to build a range with cells that contain a value of House, is that right? — DaveU, Dec 06 '13 at 06:01
Correct! That is what I am trying to do. And the thing is, when I traverse through the range, it is in the exact order as I built it which is not what I want — user3072955, Dec 06 '13 at 06:02
More info: The range is a subset of a single row, with elements in ascending order from left to right. So I was hoping to traverse through the range in an ascending order as well (but it is making me go through the order in which I built it) — user3072955, Dec 06 '13 at 06:04
The above is only a partial code. I took out all the unnecessary bits. All the variables are set up correctly and all the elements are rightly contained in HousesRange. It is just not taking me in the right order — user3072955, Dec 06 '13 at 06:05
I think it's the `After:=Cells(17, 1)` that's causing the problem. I changed it to `After:=Cells(1, 1)` in my code, and cells are selected as expected. So you need to set it above the 1st found cell. — DaveU, Dec 06 '13 at 06:18
Ah, I have to use After because my 'real' data starts below row 17. Above that are other values which I do not wish to include in my search. everything is already in the HousesRanges variable, it is just that its not going through in the right order. — user3072955, Dec 06 '13 at 06:19
Sp what you need to do is set your range to include rows 17+, and confine your search to that range. — DaveU, Dec 06 '13 at 06:24

shreyansp · Accepted Answer · 2013-12-09T23:41:14.257

1

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 using Bubblesort HouseRangeDic You should finally be able to use it in the right order:

Dim Counter As Integer
Dim Maxhouse As Integer
Dim FindHouse As Range
Dim RangeVar As Range
Dim HousesRange As Range

'****** NEW **********
Dim foundHouseCount
foundHouseCount = 1
Dim HouseRangeDic
Set HouseRangeDic = CreateObject("Scripting.dictionary")
'*********************

    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
                HouseRangeDic.Add foundHouseCount, RangeVar '**** NEW ***
                foundHouseCount = foundHouseCount + 1 '**** NEW ***
            End If
        End With
    Next Counter

    '**** NEW ***
    Bubblesort HouseRangeDic

    For i = 1 To HouseRangeDic.Count
       Application.Goto HouseRangeDic(i), True
    Next
    '************


Sub Bubblesort(ByRef rangeDic)
    Dim tempRange
    For i = 1 To rangeDic.Count - 1
        For j = i To rangeDic.Count
            If rangeDic(i).Address > rangeDic(j).Address Then
                Set tempRange = rangeDic(i)
                Set rangeDic(i) = rangeDic(j)
                Set rangeDic(j) = tempRange
            End If
        Next
    Next
End Sub

edited Dec 09 '13 at 23:41

answered Dec 06 '13 at 05:18

shreyansp

723
1
7
16

The union only has 1 row. It is made up of different cells in the same row – user3072955 Dec 06 '13 at 05:29
@user3072955 hmmm... may be I misunderstood the question. What do you mean _"first in first out basis"_. Could you post what the range in your excel sheet looks like? – shreyansp Dec 06 '13 at 05:34
So lets say that HousesRange contains [2,5,9,10]. 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 (my previous code), it takes me to 9, 10, 5 then 2. But I want it to take me to 2, 5, 9 then 10 – user3072955 Dec 06 '13 at 05:44
Here HousesRange is a subset of the row [1,2,3,4,5,6,7,8,9,10] – user3072955 Dec 06 '13 at 05:46
@user3072955 - I understand your problem now. I've posted a new solution above – shreyansp Dec 06 '13 at 08:29
Sorry about the delay in reply. but yes, an array or a dictionary is exactly what I needed. Thank you so much! – user3072955 Dec 09 '13 at 23:34
P.S. i ended up using this bubble sort code (re-coded in vba)instead. I am not sure about the one you posted for (c = 0 ; c < ( n - 1 ); c++) { for (d = 0 ; d < n - c - 1; d++) { if (array[d] > array[d+1]) /* For decreasing order use < */ { swap = array[d]; array[d] = array[d+1]; array[d+1] = swap; } } } – user3072955 Dec 10 '13 at 00:13

score 1 · Answer 2 · answered Dec 06 '13 at 06:32

1

See if this works for you. Notice my "After:=" is set to the LAST cell of the range, so the first find starts at the beginning of the range.

Sub loopCells()
    Dim FindHouse As Range
    Dim HousesRange As Range
    Dim rcell As Range
    Dim r As Range
    Dim sAdd As String
    Dim House As Long

    Set r = Sheets("Sheet1").Range("$C$15:$K$20") 'change to suit

    House = 11'change to suit
    With r

        Set FindHouse = .Find(What:=House, After:=r(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, _
            SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        If Not FindHouse Is Nothing Then
            sAdd = FindHouse.Address
            Do
               If HousesRange Is Nothing Then
                    Set HousesRange = FindHouse
                Else
                    Set HousesRange = Union(HousesRange, FindHouse)
                End If
                Set FindHouse = .FindNext(FindHouse)
            Loop While Not FindHouse Is Nothing And FindHouse.Address <> sAdd
        End If
    End With

    For Each rcell In HousesRange
        Application.Goto rcell
    Next rcell

End Sub

answered Dec 06 '13 at 06:32

DaveU

1,082
2
14
25

Nope.. same deal, I end up with the exact traverse pattern through HousesRange – user3072955 Dec 06 '13 at 06:38
It's a string variable that holds the address of the 1st Find, so you know when you've looped back to the start. Just for fun, under the `Do` line add this - `FindHouse.Select`. That way you can watch how your range is being built. Btw, what did you set your range to in the 1st line? – DaveU Dec 06 '13 at 06:46
The first line as in the line after "Counter=1"? I set that to be the first address found. After that, I union the previous found addresses with new found address. If not, which 'first line" where you referring to Dave? – user3072955 Dec 06 '13 at 06:54
No, I'm talking about my code - if you tried it, what did you set your address to in this line: `Set r = Sheets("Sheet1").Range("$C$15:$K$20")`. – DaveU Dec 06 '13 at 06:57
The thing is, HousesRange is correctly getting populated. I just cant seem to go through it properly.. and at this rate, I am going to have to write a min function -> take out the min cell from HousesRange -> repeat until HousesRange is nothing.... – user3072955 Dec 06 '13 at 07:08
So it works for me, selects the first cell on the left, and steps to the right. Sorry, not sure why it's not working for you. – DaveU Dec 06 '13 at 07:08
Dave I think i understand now, the reason it's not looping left to right is because my HouseRanges is not a complete row. Because of the separation in the middle, the order is always messed – user3072955 Dec 06 '13 at 07:47
Mines the the same way, and it works OK - even works OK if I have multiple rows with gaps. One last thing before we give up on this, can you post a sample of your data, or send it to a public drop box? Just in case there's something unique about your layout that we're missing. – DaveU Dec 06 '13 at 15:52
Hi Dave, Sorry about the delay in my reply, but using a dictionary to store the entries and then using a bubble sort on said dictionary allowed me to traverse through the entries in correct order! Thank you so much for your help though really appreciate it – user3072955 Dec 09 '13 at 23:35

MS VBA with loops and unions

2 Answers2