PDA

View Full Version : Sleeper: Can someone help me narrow down a list of combinations.



Lacan
10-18-2015, 06:09 PM
I'm in excel 2000. I'm trying to remove repeats and duplicates from this script but I'm actually completely lost.


Sub qwerty()
For i = 1 To 59
For j = 1 To 59
For k = 1 To 59
LL = LL + 1
Cells(LL, 1) = i
Cells(LL, 2) = j
Cells(LL, 3) = k
Next k
Next j
Next i
End Sub

It does everything perfectly except for repeating numbers which leads to hitting the max number of rows in excel. Is there a way to make so each combination of three is unique?

mikerickson
10-18-2015, 06:38 PM
If you want 1,2,3, but not 1,1,1 or 2,1,3, you could use


For i = 1 to 57
For j = i+1 to 58
For k = j+1 to 59

'...
Next:Next:next


A more robust answer might be "Why do you need to list all these numbers". If you are maxing out the rows on a worksheet, you're never going to look at them. So why list all of them? What is the purpose of this list?
It is possible that your goal could be achieved without this long list.

SamT
10-18-2015, 07:18 PM
For i = 1 To 59
For j = 1 To 59
For k = 1 To 59

That equals 59^3 Rows (205,379)


To get


1
2
3


4
5

6


7
8
9


10
11
12


etc
etc
etc




Use any of these:

Sub SamT()
i = 1
r = 1
Do While i <= 3 * 59
Cells(r, 1) = i
Cells(r, 2) = i + 1
Cells(r, 3) = i+ 2
r = r + 1
i = i + 3
Loop
End Sub


Sub SamT_2()
Dim Cel As Range
Dim i
i = 1

For Each Cel In Range("A1:C59")
Cel = i
i = i + 1
Next Cel
End Sub


Sub SamT_3()
Dim i
With Range("A:C")
For i = 1 To 3 * 59
.Cells(i) = i
Next i
End With
End Sub

Manually:
Enter 1, 2, & 3 in Cells A1 to C1
Enter "=A1+3" in Cell A2, Fill across to C2
Select A2 to C2, Fill down to row 59.
Copy Columns A, B, & C, PasteSpecial = Values back into them.