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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]>
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" <[email protected]> wrote in message
>news:[email protected]...
>> 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
>>
>

800 ****.. lol good stuff

never took a gre; im a dropout

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

-Aaron


Harlan Grove wrote:
> [email protected] wrote...
> ...
> >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?

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.

[email protected] wrote...
....
>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.

greaseman wrote:
> 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.
>
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.

[email protected] wrote:
> a FRAUD?
>
and useless

I'm not USELESS i'm just the only one that gives a **** abotu the 100
million spreadsheet dorks that are taking it easy; copying and pasting.

it's not a way to run a company.

it's not a good way to have a growing career

Jay;

what is this envy you've got?

you're jealous because I've got balls to do high-end SQL work and
you're stuck writing stupid little spreadsheets?

there is hope for you--

-Aaron



Jay Petrulis wrote:
> [email protected] wrote:
> > a FRAUD?
> >
> and useless

Aaron,

True...you are not useless. I say that because useless is a value and you have no value.

The only thing you ever mumble about is copying and pasting, since that is al you apparently know how to do.

The rest of us '100 million spreadsheet dorks' are quite content to USE EXCEL THE WAY IT WAS DESIGNED TO BE USED!!!!! Is your head such a vaccuum that you can't get that????????

All I can say is that I hope you have a glass belly button. Why?? Because your head is so far up your butt, that that's the only way you'll ever see anything!

[email protected] wrote...
>right click NEW in windows explorer.. create a new 'Access Application'
>
>open it up by double-clicking
>
>right-click import your spreadsheets
....

Yeah, that works so well with spreadsheets that aren't just collections
of simple tables. No doubt it exemplifies your standard work product.

I'm wearing the juice of your wives' stanky snatch

She is in love with me because I'm a playboy billionaire database
developer-- think Larry Ellison-- whereas you're a nickel-bag idiot..
working in a gas station

-Aaron
ADP Nationalist


Jay Petrulis wrote:
> [email protected] wrote:
> > great article Jay.
> >
> > of course-- it is discussing you worthless Excel dorks that use Excel
> > for everything.. right?
> >
> > because you don't know any better?
> >
>
> What are you wearing today? Lemon juice? Apple juice? Orange?
> Tomato? A smoothie?

Hey Aaron,

Shouldn't it be "skanky", not "stanky"? You also switch from plural ("your wives'") to singular ("She is...").

So, besides being a moron, and a completely ignorant one at that, you can't spell and you sucik at grammar.

My, my, my...... wouldn't you make the ideal employee (sarcasm intended).

You must've been an only child --- at least that's what your sister says.

Harlan

most spreadsheets ARE simple tables.

of course you idiots mix subtotals into your DATA. lol.

Excel should be able to have FAKE ROWS in the middle.. so that you can
display subtotals... but when someone goes to import it; it doesn't
cause problems

-Aaron


Harlan Grove wrote:
> [email protected] wrote...
> >right click NEW in windows explorer.. create a new 'Access Application'
> >
> >open it up by double-clicking
> >
> >right-click import your spreadsheets
> ...
>
> Yeah, that works so well with spreadsheets that aren't just collections
> of simple tables. No doubt it exemplifies your standard work product.

[email protected] wrote...
>most spreadsheets ARE simple tables.
....

Maybe the ones you've seen, not the ones I've seen.

>of course you idiots mix subtotals into your DATA. lol.

Subtotals in tables are unwise, but subtotals in exhibits meant to be
printed are just what's expected from subtotals.

>Excel should be able to have FAKE ROWS in the middle.. so that you can
>display subtotals... but when someone goes to import it; it doesn't
>cause problems

If you had a clue how to use Excel, you could figure out how to remove
the subtotals. It's not that difficult, but no doubt way outside your
competence.

You should always check source files before importing. Arbitrary text
files would be just as unpredictible.

Your problem is that database tables are the only data structure you're
comfortable using, so your wee brain can only conceive of other data as
tables. When they're not, you need your meds.

Harlan Grove wrote:
> ...When they're not, you need your meds.

Best taken with lemon juice.

HARLAN

THESE SIMPLIFIED DATABASE TABLES THAT YOUR TALKING ABOUT?

They're MORE POWERFUL than your baby spreadsheets.

I have worked with Excel for years and years and years.

And then I woke up and realized that it's impossible to submit a query
against 400 different spreadsheets.. so i took some classes and found
some pride.

Excel is for babies.


-Aaron

Harlan Grove wrote:
> [email protected] wrote...
> >most spreadsheets ARE simple tables.
> ...
>
> Maybe the ones you've seen, not the ones I've seen.
>
> >of course you idiots mix subtotals into your DATA. lol.
>
> Subtotals in tables are unwise, but subtotals in exhibits meant to be
> printed are just what's expected from subtotals.
>
> >Excel should be able to have FAKE ROWS in the middle.. so that you can
> >display subtotals... but when someone goes to import it; it doesn't
> >cause problems
>
> If you had a clue how to use Excel, you could figure out how to remove
> the subtotals. It's not that difficult, but no doubt way outside your
> competence.
>
> You should always check source files before importing. Arbitrary text
> files would be just as unpredictible.
>
> Your problem is that database tables are the only data structure you're
> comfortable using, so your wee brain can only conceive of other data as
> tables. When they're not, you need your meds.

[email protected] wrote:
> blah, blah, blah
>
> -Aaron
>
But you wore the juice!

[email protected] wrote...
>THESE SIMPLIFIED DATABASE TABLES THAT YOUR TALKING ABOUT?
>
>They're MORE POWERFUL than your baby spreadsheets.

Then it should be simple for you to show us how to take such simple and
powerful tables storing matrices and produce inverse matrices and
matrix products. Go on.

>I have worked with Excel for years and years and years.

And have never figured it out. Sad.

>And then I woke up and realized that it's impossible to submit a query
>against 400 different spreadsheets.. so i took some classes and found
>some pride.

Yes, and some toddlers take pride in piling up mounds of their own
excrement. For them reaching into their diapers is age-appropriate and
indicates some cleverness (even though it finds an unfortunate outlet).
You seem to take similar pride in the mounds of newsgroup excrement you
produce, but in your case it's not age-appropriate or a sign of
cleverness. As for your CLAIMED work product, you probably can't show
it to anyone other than your unfortunate clients. All you could show is
answers to questions posed in newsgroups, but you don't provide any as
a rule. Are you to scared, too lazy or too incompetent to answer
questions rather than rant?

In one of his other rantings, Aaron discussed how he went to a large company (inhis role as consultant (hah!!)) and told them how bad their data was and how he would clean it up in a matter of hours; he also told them how stupid they were. And further, Aaron, in the same rant, then discussed how that company decided not to use him!!

Aaron, Are you to scared, too lazy or too incompetent to show your CLAIMED work product???

You give new meaning to the term brain-fart.