Multiple looping structures- I'm desperate

[VBA_Newbie]

Hi folks,

I'm trying to simplify this as much as possible because my posts tend to be tedious and confusing. Suppose I have 4 columns of data...A,B,C and D. I want to compare every possible pair of combinations...even ones that might not make sense (there is a reason, but I won't get into that). I was thinking of using multiple loops that look like this:

Outer loop: Control movement to the next row once all comparisons in the two inner loops are done.

Middle Loop: Control the number of times it completes the next loop (which will not always be based on 4 columns.

Inner Loop: Makes the paired comparison.

So visually it would like like this.

Outer Loop: Do for the first row.
Middle Loop Tells it to do 4 iterations of the inner loop, because there are 4 columns in this instance.

First iteration (Inner Loop)
A-A
A-B
A-C
A-D

Second iteration of inner loop
B-A
B-B
B-C
B-D (note the basis of comparison cell changes from A to B)

Third iteration of inner loop
C-A
C-B
C-C
C-D

....and fourth iteration
D-A
D-B
D-C
D-D

Back to outer Loop: Repeat for the second row.

If this makes any sense to anyone...my problem is that I can't come up with code that would do this. So if anyone can write it down logically for me...that would be VERY helpful...I could problably figure it out from there.

 

[amigos]

not sure if that's what you need, but try belowe code and it may help you:


Sub LoopFour()

Dim i As Byte
Dim j As Byte
Dim k As Byte
Dim l As Byte

For i = 1 To 4
For j = 1 To 4
For k = 1 To 4
For l = 1 To 4

MsgBox i & j & k & l

Next l
Next k
Next j
Next i

End Sub


HTH

 

[macleanb]

I'm not exactly sure what your after PLUS I am about to leave the office (I hope) The below code is a bit dumb in that it comapres every cell to every cell - when it should really realise it has done the comparison for previous cells already - but hopefully it will give you some ideas:

Private Sub CommandButton1_Click()
Dim MyCell1
Dim MyCell2
Dim MySearchRange


MySearchRange = Range("A1:z100")


For Each MyCell1 In MySearchRange
    For Each MyCell2 In MySearchRange

        If MyCell1.Address <> MyCell1.Address Then
        
            If MyCell1 <> MyCell2 Then
                ' ?
            End If
            
        End If


    Next MyCell2
Next MyCell1



End Sub