Randomize

[gibbo1715]

Here one for you

I have a list of 100 names in sheet 1 A2:E21

Want I want to do is click a button and the names be put onto sheet2 with no more than two names on a row and at least a gap of one cell between each names (i.e. no names are next to each other and names are well spaced out around the sheet).

Is this possible?????

[mdmackillop]

Here's a bit of code to try. Create a RangeName for your data called MyNames. You can vary the spacing by playing around with the tmp function.

Sub SpreadNames()
Dim MyList(100)
m = -1
For Each cel In Range("MyNames")
m = m + 1
MyList(m) = cel
Next
Sheets("Sheet2").Select
i = Tmp
j = Tmp
For k = 0 To UBound(MyList) Step 2
i = i + Tmp
j = Tmp
Cells(i, j).Formula = MyList(k)
If k = 100 Then Exit Sub
Cells(i, j + Tmp).Formula = MyList(k + 1)
Next
End Sub
 
Function Tmp()
Tmp = Int(Rnd() * 3) + 2
End Function

[gibbo1715]

Thanks thats well on the way to what im after, is also possible to have only two names on the same column as well?

[Zack Barresse]

Malcolm, do we need to have another discussion about using Randomize? ( ROFLMAO! :rofl )

[gibbo1715]

Im confussed is there an issue with what im trying to do here?

[gibbo1715]

what about making a diamond shape with the names, say start at Cells A2 and P2 work in ward and then back out again down the rows, is that possible?

[mdmackillop]

No Gibbo, its just a dig at me!!!

A random spread over the columns is a bit more triccky, but a systematic spacing could be achieved. ie a top left to botton right diagonal distribution. But maybe some smart*** could prove me wrong!

[gibbo1715]

can you give me a clue with the systematic method then, I am learning a lot as I go here but VBA is not my language and I only started using it about a month ago so still have a lot to learn, have to admit its more powerful than i thought it was though.

I really appreciate the leg up u guys are giving me

[Zack Barresse]

(Sorry Gibbo, wasn't trying to confuse anybody. Just giving Malcolm a good ribbing - which I love to do! )

[mdmackillop]

Change

J = Tmp

to

J = J + Tmp

for an example.

[gibbo1715]

No probs im easily confussed, especially at the moment


I appreciate all the help

[gibbo1715]

Many thanks but how would i get it to go in a diamond shape?

[mdmackillop]

That's OK Zack, I can take it!

So how about a function to return all the numbers from 1 to 50 randomly with no repeats.

[mdmackillop]

Try

Sub SpreadNames()
Dim MyList(100)
m = -1
For Each cel In Range("MyNames")
m = m + 1
MyList(m) = cel
Next
Sheets("Sheet2").Select
Range("A1").Select
i = 2
j = 29
m = 29
For k = 0 To 50 Step 2
i = i + 2
j = j - 1
m = m + 1
Cells(i, j).Formula = MyList(k)
If k = 100 Then Exit Sub
Cells(i, m).Formula = MyList(k + 1)
Next
For k = 50 To 100 Step 2
i = i + 2
j = j + 1
m = m - 1
Cells(i, j).Formula = MyList(k)
If k = 100 Then Exit Sub
Cells(i, m).Formula = MyList(k + 1)
Next
End Sub

[mdmackillop]

Hi Gibbo,
Your attachment is missing.

[gibbo1715]

Sorry, here it is, also deleted the previous comment in error so hope you managed to read it and the attachment explains better what im after

[mdmackillop]

Here's a double diamond with a userform for connections, based on your code.

[mdmackillop]

BTW, I think you should put forward your MyCon function as a KB entry.

[gibbo1715]

I cant take the credit for that, it was given to me by Aaron Blood a VBAX regular

[gibbo1715]

I like what you ve done but still doesnt do what i need, the way the function i posted works is it looks at the list of names in row A, the names in rows b onwards are associates of the name in row A. It then looks at the associates and automatically creates the links between associates.

It seems a diamond will not work either for what im after as anything may be linked to anything and needs to be visible as per my original attachment, i guess random as possible will be best so your original solution may work best for me


I need to click the button and as it does in my original post