Random Number

Thanks Dave,
I got it to work just like I wanted it to. One last question, in the macro
code what would I need to put a border that is visible when printed around
each of the squares in the matrix?

"Dave Peterson" wrote:

Save the workbook with the macro and share that workbook with others.

Macros live in workbooks and you can just share the workbook with others.

But you could make it easier to run.

Just keep a single sheet in that workbook. Show the Forms toolbar
(view|toolbars) and plop a big button from that toolbar onto that single
worksheet.

Then change the caption to "Click Here To Generate 40 by 40 Matrix" (or
something you like).

Assign the macro to that big old button.

Put a couple of instructions on that worksheet and save it as nice name:
MacroWorkbookToGenerateGiantMatrix.xls

Tell the users to open the workbook (enabling macros) and click the giant
button.

Jon wrote:

Dave,
I figured out how to input the macro. Now my question is, how do I save it
so that when you open the file the macro is still there. Or if I want to send
the file to someone else will the macro go with it or do I need to have them
install it?
Thanks for the help.

"Dave Peterson" wrote:

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel to test it out.

Tools|Macro|macros|select that macro and click Run

Jon wrote:

That is exactly what I am looking for. Only 40x40. I just want it to randomly
generate the table. I saw some macro's and other stuff, but I have no idea
how to do those. Any easy ways to make this happen?

"Lori" wrote:

Permuting rows and columns only gives you a very small fraction of the
possibilities:

http://en.wikipedia.org/wiki/Latin_square

On Jan 25, 4:20 am, SteveW wrote:
Neat.

On Thu, 25 Jan 2007 03:47:33 -0000, Dave Peterson



wrote:
And in code, it would look kind of like:

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim HowMany As Long
HowMany = 40
Set wks = Workbooks.Add(1).Worksheets(1)

With wks

With .Range("A1").Offset(1, 0).Resize(HowMany, HowMany)
.Formula = "=MOD(ROW()-1+COLUMN()-1-1," & HowMany & ")+1"
End With

.Range("a1").Resize(1, HowMany).Formula = "=rand()"
.Range("a1").Offset(1, HowMany).Resize(HowMany, 1).Formula =
"=rand()"

With .UsedRange
.Value = .Value
End With
With Range("A1").Offset(1, 0).Resize(HowMany, HowMany + 1)
.Sort key1:=.Columns(HowMany + 1), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With
With .Range("a1").Resize(HowMany + 1, HowMany)
.Sort key1:=.Rows(1), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlLeftToRight
End With
.Rows(1).Delete
.Columns(HowMany + 1).Delete
.UsedRange.Columns.AutoFit

End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

(Nice idea again, Steve!)

Dave Peterson wrote:

Excellent idea.

And the OP could add =rand() to a new row 1 and sort by that.

Select A1:AN41 (avoid the column 41 with =rand() in it)

Then Data|Sort|Options button|Sort left to right.

(and when doing the sort by the 41st column, don't include that top
helper row.)

SteveW wrote:

Right in that case the rows
will have to be
1,2,3,4,...40
2,3,4,5...40,1
3,4,5...40,1,2
...
40,1,2,3...39

So that's 40 rows - the order of which can be altered
Add a helper column (41) = rand()
sort on the 41st column
Then delete it.

Steve

On Thu, 25 Jan 2007 02:00:00 -0000, Jon

wrote:

I am only looking for the numbers 1 through 40, not 1 to 1600.
Each row and column should contain no duplicates. numbers should be
in
random order.

"SteveW" wrote:

On Wed, 24 Jan 2007 23:19:17 -0000, MyVeryOwnSelf

wrote:

Is there a way to create a 40 by 40 sheet where each column and
each
row have no duplicate numbers. And have this randomly generated?

Here's one way.

In cell A1 of Sheet1, put
=RAND()
and extend right and down 40 by 40.

In cell A1 of Sheet2, put
=RANK(Sheet1!A1,Sheet1!A:A)
and extend right and down 40 by 40.

Hit the F9 key to get a new set of values.

There is is a bit in the original question that says *NO
DUPLICATES*

The question is whether that original poster wants the
numbers 1 through 1600 arranged randomly in a 40 x 40 grid
or the numbers 1 to 40 arranged in each row such that each column
has
the numbers 1 to 40

ie if it was 3x3
1 2 3 . . . 1 2 3
4 5 6 . . . 2 3 1
7 8 9 . . . 3 1 2
using the numbers 1 to 9 or 1 to 3 in eachrow/column

--
Steve (3)

--

Dave Peterson--
Steve (3)



--

Dave Peterson


--

Dave Peterson

Lots of times, it's easiest to just record a macro when you do it yourself and
tweak the code a bit.

That's what I did to get this:

Option Explicit
Sub DoBorders()
With ActiveSheet.UsedRange
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
End Sub

Right before the other macro ends, you'd add a line like:

Call DoBorders
'then finish up with
End Sub

Jon wrote:

Thanks Dave,
I got it to work just like I wanted it to. One last question, in the macro
code what would I need to put a border that is visible when printed around
each of the squares in the matrix?

"Dave Peterson" wrote:

Save the workbook with the macro and share that workbook with others.

Macros live in workbooks and you can just share the workbook with others.

But you could make it easier to run.

Just keep a single sheet in that workbook. Show the Forms toolbar
(view|toolbars) and plop a big button from that toolbar onto that single
worksheet.

Then change the caption to "Click Here To Generate 40 by 40 Matrix" (or
something you like).

Assign the macro to that big old button.

Put a couple of instructions on that worksheet and save it as nice name:
MacroWorkbookToGenerateGiantMatrix.xls

Tell the users to open the workbook (enabling macros) and click the giant
button.

Jon wrote:

Dave,
I figured out how to input the macro. Now my question is, how do I save it
so that when you open the file the macro is still there. Or if I want to send
the file to someone else will the macro go with it or do I need to have them
install it?
Thanks for the help.

"Dave Peterson" wrote:

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel to test it out.

Tools|Macro|macros|select that macro and click Run

Jon wrote:

That is exactly what I am looking for. Only 40x40. I just want it to randomly
generate the table. I saw some macro's and other stuff, but I have no idea
how to do those. Any easy ways to make this happen?

"Lori" wrote:

Permuting rows and columns only gives you a very small fraction of the
possibilities:

http://en.wikipedia.org/wiki/Latin_square

On Jan 25, 4:20 am, SteveW wrote:
Neat.

On Thu, 25 Jan 2007 03:47:33 -0000, Dave Peterson



wrote:
And in code, it would look kind of like:

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim HowMany As Long
HowMany = 40
Set wks = Workbooks.Add(1).Worksheets(1)

With wks

With .Range("A1").Offset(1, 0).Resize(HowMany, HowMany)
.Formula = "=MOD(ROW()-1+COLUMN()-1-1," & HowMany & ")+1"
End With

.Range("a1").Resize(1, HowMany).Formula = "=rand()"
.Range("a1").Offset(1, HowMany).Resize(HowMany, 1).Formula =
"=rand()"

With .UsedRange
.Value = .Value
End With
With Range("A1").Offset(1, 0).Resize(HowMany, HowMany + 1)
.Sort key1:=.Columns(HowMany + 1), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With
With .Range("a1").Resize(HowMany + 1, HowMany)
.Sort key1:=.Rows(1), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlLeftToRight
End With
.Rows(1).Delete
.Columns(HowMany + 1).Delete
.UsedRange.Columns.AutoFit

End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

(Nice idea again, Steve!)

Dave Peterson wrote:

Excellent idea.

And the OP could add =rand() to a new row 1 and sort by that.

Select A1:AN41 (avoid the column 41 with =rand() in it)

Then Data|Sort|Options button|Sort left to right.

(and when doing the sort by the 41st column, don't include that top
helper row.)

SteveW wrote:

Right in that case the rows
will have to be
1,2,3,4,...40
2,3,4,5...40,1
3,4,5...40,1,2
...
40,1,2,3...39

So that's 40 rows - the order of which can be altered
Add a helper column (41) = rand()
sort on the 41st column
Then delete it.

Steve

On Thu, 25 Jan 2007 02:00:00 -0000, Jon

wrote:

I am only looking for the numbers 1 through 40, not 1 to 1600.
Each row and column should contain no duplicates. numbers should be
in
random order.

"SteveW" wrote:

On Wed, 24 Jan 2007 23:19:17 -0000, MyVeryOwnSelf

wrote:

Is there a way to create a 40 by 40 sheet where each column and
each
row have no duplicate numbers. And have this randomly generated?

Here's one way.

In cell A1 of Sheet1, put
=RAND()
and extend right and down 40 by 40.

In cell A1 of Sheet2, put
=RANK(Sheet1!A1,Sheet1!A:A)
and extend right and down 40 by 40.

Hit the F9 key to get a new set of values.

There is is a bit in the original question that says *NO
DUPLICATES*

The question is whether that original poster wants the
numbers 1 through 1600 arranged randomly in a 40 x 40 grid
or the numbers 1 to 40 arranged in each row such that each column
has
the numbers 1 to 40

ie if it was 3x3
1 2 3 . . . 1 2 3
4 5 6 . . . 2 3 1
7 8 9 . . . 3 1 2
using the numbers 1 to 9 or 1 to 3 in eachrow/column

--
Steve (3)

--

Dave Peterson--
Steve (3)



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson