Beginner question

  • Thread starter Thread starter Tony Vella
  • Start date Start date
T

Tony Vella

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.
 
I'm sure there are tons of ways to do this. Here's one.


- Enter the numbers 1 and 2 in cells A1 and B1 respectively
- Highlight cells A1 and B1
- Right click hold the small square in the lower right hand corner o
the highlighted range.
- Drag over 8 more columns, let the mouse button go and select fil
series
- Now select cell A2 and input =A1+10
- Copy Cell A2 to Cells A2:J10
- copy the entire range and paste values over the
 
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
 
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
 
beginner;

run away while you still can. Excel is a waste of time.

Spit on people that use Excel.

-Aaron
 
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

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
Click to expand...
 
will Excel even hold that many values?

-Aaron


Gord said:
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
Click to expand...
 
(e-mail address removed) wrote...
will Excel even hold that many values?
Click to expand...

Why don't you start using your fingers and toes. That'd be good for the
first 24 or so.
 
and for the record?

I would rather use pen and paper than excel

i mean-- draw it out; do the math in my head

btw, what did you score on your math SAT prick??
 
800 shit.. lol good stuff

never took a gre; im a dropout

I've spent $30 grand in classes in the past 5 years though

-Aaron
 
what.. because I'm not a worthless Excel dork?

Just because I spent classes on REAL PROGRAMMNIG LANGUAGES that help me
to write REAL TOOLS?

you write reports-- a la documents.

I write reports-- a la tools.

you do the same goddamn spreadsheet week in and week out.

Excel isn't the right tool for anything.

And your stupid MMULT? i can use that in analysis services out of the
box.

-Aaron
 
Since this thread is quite new, thought I'd give you all a quick heads
up:

Aaron and Harlan have been engaged in their ridiculous tete-a-tete for
over a year, now. As you can easily see from how Aaron responds, any
replies he gives to you are completely useless. And since Harlan never
responds to anyone in this forum, his replies are useless as well.

All I can really say about Aaron is that he is one of those clowns who
brags a lot and says little. As in the case here, where he claims to
have spent $30,000 over five years on classes. He definitely learned
nothing about anything.

Incidentally, he was fired from Microsoft for two reasons: his
ineptitude and his mouth. Do a search on "Aaron" or "Aaron Kempf" or
"dbaHooker" in this forum and you will see some pathetic examples of
his garbage.
 
(e-mail address removed) wrote...
....
And your stupid MMULT? i can use that in analysis services out of the
box.
Click to expand...

Really?! How? Analysis Services/MDX lacks its own MMULT function, and
it's Excel function library also lacks it. So how would you perform
matrix multiplication with Analysis Services? Provide some details . .
.. if you're able.
 
greaseman

don't discredit me.

I am trying to save these Excel dorks from obsolescence.

Excel is a dead end street-- run away while you still can.

Grow some balls and learn a real program like Crystal Reports or SQL
Server.. or if you're adamant that Excel is the future? Then learn
MDX. MDX is Olap times Excel-- it is a great language.

Sitting on your ass and still being a one-trick pony?

Excel is NOT a real development platform.
It DOESNT have real datatype validation.

It DOESNT have real data entry tools.

The data format-- Excel-- is not open or performant or reliable.

Multiple people can't use the same XLS at the same time.

On a scale of 1 to 10? Excel scores a ZERO.

Spit on people that use Excel.

Cut off their weiners-- so that they stop breeding.

Excel is a disease. I spit on you lepers.

Yes; I was fired from Microsoft-- for standing up for what is right.
2/3rds of the companies that I've ever been at-- use SQL
authentication. Microsoft takes this -tough-guy- tact--- their opinion
is '**** sql authentication'

Nobody outside of Microsoft knows how to configure double-hop
authentication.

And thus-- a TON of companies use SQL authentication-- even though it
can't stand up to a simple dictionary attack.

For the record?? I MIGHT BE HEADING BACK TO MICROSOFT SOON.

And let's just say that they're not having me in there for my 'Excel
Skillz'

Excel isn't a SKILL. It is a crutch; a tool and a disease.

Spreadsheet people can lick my NUTS.

If I had my way; you would be drinking whiskey out of a paper bag on
148th and Northrup.

I would pull over and SPIT on you.

'WILL SPREADSHEET FOR FOOD'

GAG ME WITH A SPOON
 
greaseman said:
Since this thread is quite new, thought I'd give you all a quick heads
up:

Aaron and Harlan have been engaged in their ridiculous tete-a-tete for
over a year, now. As you can easily see from how Aaron responds, any
replies he gives to you are completely useless. And since Harlan never
responds to anyone in this forum, his replies are useless as well.
Click to expand...
I think everyone wopuld agree with you 100% that Aaron is useless (even
Aaron). However, please explain the Harlan comment.

No need for me to defend Harlan (this is my cue to defend Harlan).
Harlan can be challenging to deal with and would likely admit that
himself, but he provides a ton of help and usually responds quickly.
Take his tough-love and you can learn a lot.

In these threads, Harlan is merely exposing Aaron as a fraud. Sure, it
detracts from the group, but some of us (me) enjoy the Aaron Kempf
trainwrecks.
 
a FRAUD?

I'm not the MONKEY that gets paid for repetitive copy and paste.

your jobs should be in India.. and I can't wait till people start
taking Excel seriously as a real Software Development platform.

Until then you guys can take your spaghetti code and shove it.

Million Dollar Spreadsheets that are stuck as single-user reports?

LAUGHABLE.

-Aaron
 
Back
Top