Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.programming




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




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




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




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 
Display Modes  


Similar Threads  
Thread  Forum  
Incrementing Rows and Columns  Excel Discussion (Misc queries)  
incrementing every two rows  Excel Programming  
incrementing columns in a loop  Excel Programming  
Incrementing formula by two rows  Excel Worksheet Functions  
Incrementing rows (or columns) to a reference in another worksheet  Excel Worksheet Functions 