Hi, is there a way to make this table in VBA?

Mon Tue Wed Thu Fri
A 1 2 3 4 5
B 2 1 5 3 4
C 3 4 1 5 2
D 4 5 2 1 3
E 5 3 4 2 1

What i need is a table with 5 schedules that can never repeat for one person
(A, B, C, D or E) in the week.
There are only 5 schedules in every day that should be distributed for the 5
persons

Thanks

Iforgot to say that i've been using this macro to random the schedules but
the problem is that it repeats the schedules:



Sub Gen5()
Dim varr(1 To 5) As Long
Dim varr1(1 To 5) As Long
Dim varr2(1 To 5) As Long
Dim varr3(1 To 5) As Long
Dim varr4(1 To 5) As Long

'colunas
Dim list(1 To 5) As Long
Dim list1(1 To 5) As Long
Dim list2(1 To 5) As Long
Dim list3(1 To 5) As Long
Dim list4(1 To 5) As Long

Dim i As Long, j As Long
Dim num As Long
For i = 1 To 5
varr(i) = i
varr1(i) = i
varr2(i) = i
varr3(i) = i
varr4(i) = i
Next

j = 1
Do While j < 6
num = Int(Rnd * 5 + 1)
If varr(num) < 0 Then
list(j) = varr(num)
varr(num) = 0
j = j + 1
End If
Loop

j = 1
Do While j < 6
num = Int(Rnd * 5 + 1)
If varr1(num) < 0 Then
list1(j) = varr1(num)
varr1(num) = 0
j = j + 1
End If
Loop

j = 1
Do While j < 6
num = Int(Rnd * 5 + 1)
If varr2(num) < 0 Then
list2(j) = varr2(num)
varr2(num) = 0
j = j + 1
End If
Loop

j = 1
Do While j < 6
num = Int(Rnd * 5 + 1)
If varr3(num) < 0 Then
list3(j) = varr3(num)
varr3(num) = 0
j = j + 1
End If
Loop

j = 1
Do While j < 6
num = Int(Rnd * 5 + 1)
If varr4(num) < 0 Then
list4(j) = varr4(num)
varr4(num) = 0
j = j + 1
End If
Loop



For i = 1 To 5
Cells(i, 1) = list(i)
Cells(i, 2) = list1(i)
Cells(i, 3) = list2(i)
Cells(i, 4) = list3(i)
Cells(i, 5) = list4(i)

Next

End Sub





"Pedro Costa" escreveu:

Hi, is there a way to make this table in VBA?

Mon Tue Wed Thu Fri
A 1 2 3 4 5
B 2 1 5 3 4
C 3 4 1 5 2
D 4 5 2 1 3
E 5 3 4 2 1

What i need is a table with 5 schedules that can never repeat for one person
(A, B, C, D or E) in the week.
There are only 5 schedules in every day that should be distributed for the 5
persons

Thanks

Sub aa()
Dim v(1 To 5) As Variant
Dim v1 As Variant
v(1) = Array(1, 2, 3, 4, 5)
v(2) = Array(5, 1, 2, 3, 4)
v(3) = Array(4, 5, 1, 2, 3)
v(4) = Array(3, 4, 5, 1, 2)
v(5) = Array(2, 3, 4, 5, 1)

For i = 1 To 10
j = Int(Rnd() * 5 + 1)
k = Int(Rnd() * 5 + 1)
If j < k Then
v1 = v(j)
v(j) = v(k)
v(k) = v1
End If
Next
For i = 1 To 5
Cells(i, 1).Resize(1, 5) = v(i)
Next
End Sub

--
Regards,
Tom Ogilvy



"Pedro Costa" wrote in message
...
Iforgot to say that i've been using this macro to random the schedules but
the problem is that it repeats the schedules:



Sub Gen5()
Dim varr(1 To 5) As Long
Dim varr1(1 To 5) As Long
Dim varr2(1 To 5) As Long
Dim varr3(1 To 5) As Long
Dim varr4(1 To 5) As Long

'colunas
Dim list(1 To 5) As Long
Dim list1(1 To 5) As Long
Dim list2(1 To 5) As Long
Dim list3(1 To 5) As Long
Dim list4(1 To 5) As Long

Dim i As Long, j As Long
Dim num As Long
For i = 1 To 5
varr(i) = i
varr1(i) = i
varr2(i) = i
varr3(i) = i
varr4(i) = i
Next

j = 1
Do While j < 6
num = Int(Rnd * 5 + 1)
If varr(num) < 0 Then
list(j) = varr(num)
varr(num) = 0
j = j + 1
End If
Loop

j = 1
Do While j < 6
num = Int(Rnd * 5 + 1)
If varr1(num) < 0 Then
list1(j) = varr1(num)
varr1(num) = 0
j = j + 1
End If
Loop

j = 1
Do While j < 6
num = Int(Rnd * 5 + 1)
If varr2(num) < 0 Then
list2(j) = varr2(num)
varr2(num) = 0
j = j + 1
End If
Loop

j = 1
Do While j < 6
num = Int(Rnd * 5 + 1)
If varr3(num) < 0 Then
list3(j) = varr3(num)
varr3(num) = 0
j = j + 1
End If
Loop

j = 1
Do While j < 6
num = Int(Rnd * 5 + 1)
If varr4(num) < 0 Then
list4(j) = varr4(num)
varr4(num) = 0
j = j + 1
End If
Loop



For i = 1 To 5
Cells(i, 1) = list(i)
Cells(i, 2) = list1(i)
Cells(i, 3) = list2(i)
Cells(i, 4) = list3(i)
Cells(i, 5) = list4(i)

Next

End Sub





"Pedro Costa" escreveu:

Hi, is there a way to make this table in VBA?

Mon Tue Wed Thu Fri
A 1 2 3 4 5
B 2 1 5 3 4
C 3 4 1 5 2
D 4 5 2 1 3
E 5 3 4 2 1

What i need is a table with 5 schedules that can never repeat for one
person
(A, B, C, D or E) in the week.
There are only 5 schedules in every day that should be distributed for
the 5
persons

Thanks

Thank you very much for your post, it works just the way i need ...

And if i want to assign a certain schedule to a certain week day, is it
possible to have a macro to fill the cells that are empty?

Thanks


"Pedro Costa" escreveu:

Hi, is there a way to make this table in VBA?

Mon Tue Wed Thu Fri
A 1 2 3 4 5
B 2 1 5 3 4
C 3 4 1 5 2
D 4 5 2 1 3
E 5 3 4 2 1

What i need is a table with 5 schedules that can never repeat for one person
(A, B, C, D or E) in the week.
There are only 5 schedules in every day that should be distributed for the 5
persons

Thanks