Remember Me?

#1
Posted to microsoft.public.excel.programming
 external usenet poster Posts: 3
Incrementing Rows in columns

I am trying to set up a macros that will increment the referenced row as the
columns are dragged down. The only problem is that I want the rows to remain
steadfast so that I can then drag them across the page without having them
increment. How can I do that? Hopefully this will give you an example:

In A20:
=sum(\$a\$1:\$a\$10)*sum(a\$1:a\$10)

Then in B20:
=sum(\$a\$1:\$a\$10)*sum(b\$1:b\$10)

And C20:
=sum(\$a\$1:\$a\$10)*sum(c\$1:c\$10)

But in A21:
=sum(\$b\$1:\$b\$10)*sum(a\$1:a\$10)

And in B21:
=sum(\$b\$1:\$b\$10)*sum(b\$1:b\$10)

and c21:
=sum(\$b\$1:\$b\$10)*sum(c\$1:c\$10)

It would be really nice to be able to drag this and make it work, and/or
write macros to do it rather than doing it by hand.

Thanks a TON!!!
#2
Posted to microsoft.public.excel.programming
 external usenet poster Posts: 325
Incrementing Rows in columns

Ray,

I'm not sure I quite understand what you're getting at, but have you thought
of creating names in your worksheet, then referencing the names in your code,
as against cell addresses?

This means that if the ranges (e.g. a1:10) are moved (to B15:B24 for
example), or have rows inserted into them (making your range a1:a12, for
example) or deleted from them, the code will still work on the range of cells
to which the nam applies. This is because names follow the cell ranges and
reflect whether they've moved, or grown/shrunk.

Sorry to be dim,. but toes this help at all with what you're trying to do?

Regards

Pete

"Ray via OfficeKB.com" wrote:

I am trying to set up a macros that will increment the referenced row as the
columns are dragged down. The only problem is that I want the rows to remain
steadfast so that I can then drag them across the page without having them
increment. How can I do that? Hopefully this will give you an example:

In A20:
=sum(\$a\$1:\$a\$10)*sum(a\$1:a\$10)

Then in B20:
=sum(\$a\$1:\$a\$10)*sum(b\$1:b\$10)

And C20:
=sum(\$a\$1:\$a\$10)*sum(c\$1:c\$10)

But in A21:
=sum(\$b\$1:\$b\$10)*sum(a\$1:a\$10)

And in B21:
=sum(\$b\$1:\$b\$10)*sum(b\$1:b\$10)

and c21:
=sum(\$b\$1:\$b\$10)*sum(c\$1:c\$10)

It would be really nice to be able to drag this and make it work, and/or
write macros to do it rather than doing it by hand.

Thanks a TON!!!

#3
Posted to microsoft.public.excel.programming
 external usenet poster Posts: 1,163
Incrementing Rows in columns

If I understand, this might be what you want (put this formula in A20 and you
can drag it down the rows in column A and then across the columns):
=SUM(OFFSET(\$A\$1:\$A\$10,0,ROW()-20))*SUM(A\$1:A\$10)

--
- K Dales

"Ray via OfficeKB.com" wrote:

I am trying to set up a macros that will increment the referenced row as the
columns are dragged down. The only problem is that I want the rows to remain
steadfast so that I can then drag them across the page without having them
increment. How can I do that? Hopefully this will give you an example:

In A20:
=sum(\$a\$1:\$a\$10)*sum(a\$1:a\$10)

Then in B20:
=sum(\$a\$1:\$a\$10)*sum(b\$1:b\$10)

And C20:
=sum(\$a\$1:\$a\$10)*sum(c\$1:c\$10)

But in A21:
=sum(\$b\$1:\$b\$10)*sum(a\$1:a\$10)

And in B21:
=sum(\$b\$1:\$b\$10)*sum(b\$1:b\$10)

and c21:
=sum(\$b\$1:\$b\$10)*sum(c\$1:c\$10)

It would be really nice to be able to drag this and make it work, and/or
write macros to do it rather than doing it by hand.

Thanks a TON!!!

#4
Posted to microsoft.public.excel.programming
 external usenet poster Posts: 3
Incrementing Rows in columns

Thanks for your help guys. I don't know if I am explaining this correctly,
so i will try one more time. This is what I am working with.

{=COUNT(IF((\$C\$11:\$C\$128620)*(C\$11:C\$128620),0)) }

Step 1:
Make the formula in the row below the initial one increment the column
referenced in the first part of the formula (change \$C\$11:\$C\$12862 to \$D\$11:
\$D\$12862), but leave the reference in the second part the same. I can do
this by hand, but am looking for a way to program it to eliminate the manual
part. IE:

Drag it down one row to make the formula:
{=COUNT(IF((\$D\$11:\$D\$128620)*(C\$11:C\$128620),0)) }

Step 2:
I understand this and I have it working in both regular Excel and with an
autofill command in VBA, but there might be a reason to increment this as
well to make Step 1 run more smoothly, I just don't know enough about it.

Drag that (From Step 1) to the right one column to make the formula:
{=COUNT(IF((\$D\$11:\$D\$128620)*(D\$11:D\$128620),0)) }

I want to find a way to automate the change in Step 1 either through marcos
autofill or through draging it down.

Thanks again folks!!!!!

K Dales wrote:
If I understand, this might be what you want (put this formula in A20 and you
can drag it down the rows in column A and then across the columns):
=SUM(OFFSET(\$A\$1:\$A\$10,0,ROW()-20))*SUM(A\$1:A\$10)

I am trying to set up a macros that will increment the referenced row as the
columns are dragged down. The only problem is that I want the rows to remain

[quoted text clipped - 23 lines]

Thanks a TON!!!

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Intern Excel Discussion (Misc queries) 1 June 5th 09 05:59 PM childofthe1980s Excel Programming 3 June 25th 05 12:09 AM Mitch Excel Programming 1 June 9th 05 03:40 PM Ben Hur Excel Worksheet Functions 1 February 24th 05 09:21 PM Ignobilitor Excel Worksheet Functions 2 January 20th 05 03:45 PM

All times are GMT +1. The time now is 03:54 AM.