Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 325
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,163
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default 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.

Start 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!!!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Incrementing Rows and Columns Intern Excel Discussion (Misc queries) 1 June 5th 09 05:59 PM
incrementing every two rows childofthe1980s Excel Programming 3 June 25th 05 12:09 AM
incrementing columns in a loop Mitch Excel Programming 1 June 9th 05 03:40 PM
Incrementing formula by two rows Ben Hur Excel Worksheet Functions 1 February 24th 05 09:21 PM
Incrementing rows (or columns) to a reference in another worksheet Ignobilitor Excel Worksheet Functions 2 January 20th 05 03:45 PM


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

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"