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.
from 1 to 100? I am using Excel 97 on an XP Home machine. Thanks in
advance.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
E
Excelenator
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
- 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
B
Biff
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
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
L
L. Howard Kittle
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
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
A
aaron.kempf
beginner;
run away while you still can. Excel is a waste of time.
Spit on people that use Excel.
-Aaron
run away while you still can. Excel is a waste of time.
Spit on people that use Excel.
-Aaron
G
Gord Dibben
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
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
A
aaron.kempf
will Excel even hold that many values?
-Aaron
-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
H
Harlan Grove
(e-mail address removed) wrote...
Why don't you start using your fingers and toes. That'd be good for the
first 24 or so.
will Excel even hold that many values?
Why don't you start using your fingers and toes. That'd be good for the
first 24 or so.
D
dbahooker
good stuff
Harlan said:(e-mail address removed) wrote...
Why don't you start using your fingers and toes. That'd be good for the
first 24 or so.
D
dbahooker
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??
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??
H
Harlan Grove
(e-mail address removed) wrote...
....
No doubt produce as many errors, too.
800. What'd you score on your math GRE?
....
....I would rather use pen and paper than excel
No doubt produce as many errors, too.
btw, what did you score on your math SAT prick??
800. What'd you score on your math GRE?
A
aaron.kempf
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
never took a gre; im a dropout
I've spent $30 grand in classes in the past 5 years though
-Aaron
H
Harlan Grove
(e-mail address removed) wrote...
....
Wasted.
....
I've spent $30 grand in classes in the past 5 years though
Wasted.
A
aaron.kempf
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
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
G
greaseman
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.
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.
H
Harlan Grove
(e-mail address removed) wrote...
....
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.
....
And your stupid MMULT? i can use that in analysis services out of the
box.
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.
A
aaron.kempf
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
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
J
Jay Petrulis
I think everyone wopuld agree with you 100% that Aaron is useless (evengreaseman 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.
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
aaron.kempf
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
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
A
aaron.kempf
greaseman
btw; i've got TONS of other aliases.. you can't find them?
-Aaron
btw; i've got TONS of other aliases.. you can't find them?
-Aaron
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.