Beginner question

Can I have the program automatically fill 10 columns of 10 rows with numbers
from 1 to 100? I am using Excel 97 on an XP Home machine. Thanks in
advance.
--
Tony Vella in Ottawa, Canada

Hi!

How do you want the output?

1...........10
11.........20

Or:

1...11...21
2...12...22

For the first example, enter this formula in top left cell of the grid:

=COLUMNS($A:A)+(ROWS($1:1)-1)*10

For the second example:

=ROWS($1:1)+(COLUMNS($A:A)-1)*10

Copy the formula across to 10 columns then down 10 rows.

Then, if you want to get rid of the formulas and convert them to constants:

Select the grid of cells
Goto Edit>Copy
Then, Edit>Paste Special>Values>OK

Biff

"Tony Vella" <tony.vella@rogers.com> wrote in message
news:waWdnYwGzpVPNU_ZnZ2dnUVZ_s2dnZ2d@giganews.com...
> Can I have the program automatically fill 10 columns of 10 rows with
> numbers
> from 1 to 100? I am using Excel 97 on an XP Home machine. Thanks in
> advance.
> --
> Tony Vella in Ottawa, Canada
>

Hi Tony,

Here's a couple macros, one for numbers across and one for numbers down.
For numbers across you will need to name a 10 by 10 cell block Data, or any
name you wish, and then change Data in the code to your name.

Sub NumbersAcross()
Dim Cell As Range
Dim i As Integer
i = 1
For Each Cell In Range("Data")
Cell.Value = i
i = i + 1
Next
End Sub

Sub NumbersDown()
Dim i As Integer
Dim Cell As Range
Range("A1").Select
For i = 1 To 100
ActiveCell.Value = i
ActiveCell.Offset(1, 0).Select
If ActiveCell.Row = 11 Then
ActiveCell.Offset(-10, 1).Select
End If
Next
End Sub

HTH
Regards,
Howard

"Tony Vella" <tony.vella@rogers.com> wrote in message
news:waWdnYwGzpVPNU_ZnZ2dnUVZ_s2dnZ2d@giganews.com...
> Can I have the program automatically fill 10 columns of 10 rows with
> numbers
> from 1 to 100? I am using Excel 97 on an XP Home machine. Thanks in
> advance.
> --
> Tony Vella in Ottawa, Canada
>

Here's a macro that allows you to choose your orientation and range.

Who knows.......maybe some day you will need 115 x 186 matrix<g>

Sub FillNums()
'to fill rows and columns with numbers from 1 to whatever
Dim nrows As Integer
Dim ncols As Integer
On Error GoTo quitnow
RowsorCols = InputBox("Fill Across = 1" & Chr(13) _
& "Fill Down = 2")
Num = 1
nrows = InputBox("Enter Number of Rows")
ncols = InputBox("Enter Number of Columns")
If RowsorCols = 1 Then
For across = 1 To nrows
For down = 1 To ncols
ActiveSheet.Cells(across, down).Value = Num
Num = Num + 1
Next down
Next across
Else
For across = 1 To ncols
For down = 1 To nrows
ActiveSheet.Cells(down, across).Value = Num
Num = Num + 1
Next down
Next across
End If
quitnow:
End Sub


Gord Dibben MS Excel MVP

On Fri, 4 Aug 2006 14:01:31 -0700, "L. Howard Kittle" <lhkittle@comcast.net>
wrote:

>Hi Tony,
>
>Here's a couple macros, one for numbers across and one for numbers down.
>For numbers across you will need to name a 10 by 10 cell block Data, or any
>name you wish, and then change Data in the code to your name.
>
>Sub NumbersAcross()
>Dim Cell As Range
>Dim i As Integer
>i = 1
>For Each Cell In Range("Data")
> Cell.Value = i
> i = i + 1
>Next
>End Sub
>
>Sub NumbersDown()
>Dim i As Integer
>Dim Cell As Range
>Range("A1").Select
>For i = 1 To 100
> ActiveCell.Value = i
> ActiveCell.Offset(1, 0).Select
> If ActiveCell.Row = 11 Then
> ActiveCell.Offset(-10, 1).Select
> End If
>Next
>End Sub
>
>HTH
>Regards,
>Howard
>
>"Tony Vella" <tony.vella@rogers.com> wrote in message
>news:waWdnYwGzpVPNU_ZnZ2dnUVZ_s2dnZ2d@giganews.com...
>> Can I have the program automatically fill 10 columns of 10 rows with
>> numbers
>> from 1 to 100? I am using Excel 97 on an XP Home machine. Thanks in
>> advance.
>> --
>> Tony Vella in Ottawa, Canada
>>
>