Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extending a macro
Here is a simple macro I am using:
Sub Rows4() ' ' Rows4 Macro ' Macro recorded 4/20/2007 by Administrator ' ' ActiveCell.Select ActiveCell.FormulaR1C1 = "=RC[-6]" ActiveCell.Offset(0, 2).Range("A1").Select ActiveCell.FormulaR1C1 = "=SUM(R[-3]C[-6]:RC[-6])" ActiveCell.Offset(0, 2).Range("A1").Select ActiveCell.FormulaR1C1 = "=SUM(R[-3]C[-7]:RC[-7])" ActiveCell.Offset(1, -4).Range("A1").Select End Sub It is one of a family of 11 macros, each named "Rows[n]", and all I have to do is count how many rows I need to apply a macro to, and call the macro named for that number of rows with its hotkey. However, being fundamentally lazy (always give the hardest task to the laziest employee, and he/she will always find the easiest way to do it <g) -- I want to extend it a tiny bit. I would like to change that last line to: ActiveCell.Offset(1, -10).Range("A1").Select This column contains years, sequentially sorted. So there could be four 1996 then five 1997, then eleven 1998 then two 1996 again. What I need to do, after making that select is this: --Capture the value in the selected cell --Scroll down, counting the rows until I find a different value --Scroll back up one row, and hold the adjusted count for number of rows --Scroll back to the right 6 columns to my "starting cell" --Invoke the Macro based on the number of rows counted ("Rows3" e.g.) The table has nearly 9,000 rows, and doing this junk manually I have reached row 800 in some two hours. If I could modify the macros to do this, it would do the whole shebang in a couple of heartbeats! All contributions gratefully received. Dave Temping with Staffmark in Rock Hill, SC |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extending a macro
Dave,
Try using the subtotal feature of Excel. Select your table, then use Data / Subtotals.... and pick your coulmn with year as the "At each change in.." column, and the sum your columns as desired. And if there are other columns of identifiers, consider using a Pivot table... HTH, Bernie MS Excel MVP "Dave Birley" wrote in message ... Here is a simple macro I am using: Sub Rows4() ' ' Rows4 Macro ' Macro recorded 4/20/2007 by Administrator ' ' ActiveCell.Select ActiveCell.FormulaR1C1 = "=RC[-6]" ActiveCell.Offset(0, 2).Range("A1").Select ActiveCell.FormulaR1C1 = "=SUM(R[-3]C[-6]:RC[-6])" ActiveCell.Offset(0, 2).Range("A1").Select ActiveCell.FormulaR1C1 = "=SUM(R[-3]C[-7]:RC[-7])" ActiveCell.Offset(1, -4).Range("A1").Select End Sub It is one of a family of 11 macros, each named "Rows[n]", and all I have to do is count how many rows I need to apply a macro to, and call the macro named for that number of rows with its hotkey. However, being fundamentally lazy (always give the hardest task to the laziest employee, and he/she will always find the easiest way to do it <g) -- I want to extend it a tiny bit. I would like to change that last line to: ActiveCell.Offset(1, -10).Range("A1").Select This column contains years, sequentially sorted. So there could be four 1996 then five 1997, then eleven 1998 then two 1996 again. What I need to do, after making that select is this: --Capture the value in the selected cell --Scroll down, counting the rows until I find a different value --Scroll back up one row, and hold the adjusted count for number of rows --Scroll back to the right 6 columns to my "starting cell" --Invoke the Macro based on the number of rows counted ("Rows3" e.g.) The table has nearly 9,000 rows, and doing this junk manually I have reached row 800 in some two hours. If I could modify the macros to do this, it would do the whole shebang in a couple of heartbeats! All contributions gratefully received. Dave Temping with Staffmark in Rock Hill, SC |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extending a macro
Sorry, but I'm a VFP and xBase programmer from way back (that's why I know
what I would like <g) but this is the first time in my life I have done anything that looks like programming in Excel. I like the idea of "At each change in...", but when I entered the configuration dialog, I thought I could use Count -- pass the results to a new column created to receive them. Didn't work. Thank goodness for Ctrl-Z <G! -- Dave Temping with Staffmark in Rock Hill, SC "Bernie Deitrick" wrote: Dave, Try using the subtotal feature of Excel. Select your table, then use Data / Subtotals.... and pick your coulmn with year as the "At each change in.." column, and the sum your columns as desired. And if there are other columns of identifiers, consider using a Pivot table... HTH, Bernie MS Excel MVP "Dave Birley" wrote in message ... Here is a simple macro I am using: Sub Rows4() ' ' Rows4 Macro ' Macro recorded 4/20/2007 by Administrator ' ' ActiveCell.Select ActiveCell.FormulaR1C1 = "=RC[-6]" ActiveCell.Offset(0, 2).Range("A1").Select ActiveCell.FormulaR1C1 = "=SUM(R[-3]C[-6]:RC[-6])" ActiveCell.Offset(0, 2).Range("A1").Select ActiveCell.FormulaR1C1 = "=SUM(R[-3]C[-7]:RC[-7])" ActiveCell.Offset(1, -4).Range("A1").Select End Sub It is one of a family of 11 macros, each named "Rows[n]", and all I have to do is count how many rows I need to apply a macro to, and call the macro named for that number of rows with its hotkey. However, being fundamentally lazy (always give the hardest task to the laziest employee, and he/she will always find the easiest way to do it <g) -- I want to extend it a tiny bit. I would like to change that last line to: ActiveCell.Offset(1, -10).Range("A1").Select This column contains years, sequentially sorted. So there could be four 1996 then five 1997, then eleven 1998 then two 1996 again. What I need to do, after making that select is this: --Capture the value in the selected cell --Scroll down, counting the rows until I find a different value --Scroll back up one row, and hold the adjusted count for number of rows --Scroll back to the right 6 columns to my "starting cell" --Invoke the Macro based on the number of rows counted ("Rows3" e.g.) The table has nearly 9,000 rows, and doing this junk manually I have reached row 800 in some two hours. If I could modify the macros to do this, it would do the whole shebang in a couple of heartbeats! All contributions gratefully received. Dave Temping with Staffmark in Rock Hill, SC |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extending a macro
Dave,
I thought that since your macro was using SUM you wanted sums.... Anyway, this macro assumes that your years are in column A, and you want your formulas in columns H, J, and L. (But you never described your actual layout.) It will do all the data at one time. Sub TryNow() Dim myCount As Integer Dim myCell As Range myCount = 0 For Each myCell In Range("A2", Range("A65536").End(xlUp)) If myCell.Value < myCell(2, 1).Value Then myCell(1, 8).FormulaR1C1 = "=RC[-6]" myCell(1, 10).FormulaR1C1 = "=SUM(R[-" & myCount & "]C[-6]:RC[-6])" myCell(1, 12).FormulaR1C1 = "=SUM(R[-" & myCount & "]C[-7]:RC[-7])" myCount = 0 Else myCount = myCount + 1 End If Next myCell End Sub HTH, Bernie MS Excel MVP "Dave Birley" wrote in message ... Sorry, but I'm a VFP and xBase programmer from way back (that's why I know what I would like <g) but this is the first time in my life I have done anything that looks like programming in Excel. I like the idea of "At each change in...", but when I entered the configuration dialog, I thought I could use Count -- pass the results to a new column created to receive them. Didn't work. Thank goodness for Ctrl-Z <G! -- Dave Temping with Staffmark in Rock Hill, SC "Bernie Deitrick" wrote: Dave, Try using the subtotal feature of Excel. Select your table, then use Data / Subtotals.... and pick your coulmn with year as the "At each change in.." column, and the sum your columns as desired. And if there are other columns of identifiers, consider using a Pivot table... HTH, Bernie MS Excel MVP "Dave Birley" wrote in message ... Here is a simple macro I am using: Sub Rows4() ' ' Rows4 Macro ' Macro recorded 4/20/2007 by Administrator ' ' ActiveCell.Select ActiveCell.FormulaR1C1 = "=RC[-6]" ActiveCell.Offset(0, 2).Range("A1").Select ActiveCell.FormulaR1C1 = "=SUM(R[-3]C[-6]:RC[-6])" ActiveCell.Offset(0, 2).Range("A1").Select ActiveCell.FormulaR1C1 = "=SUM(R[-3]C[-7]:RC[-7])" ActiveCell.Offset(1, -4).Range("A1").Select End Sub It is one of a family of 11 macros, each named "Rows[n]", and all I have to do is count how many rows I need to apply a macro to, and call the macro named for that number of rows with its hotkey. However, being fundamentally lazy (always give the hardest task to the laziest employee, and he/she will always find the easiest way to do it <g) -- I want to extend it a tiny bit. I would like to change that last line to: ActiveCell.Offset(1, -10).Range("A1").Select This column contains years, sequentially sorted. So there could be four 1996 then five 1997, then eleven 1998 then two 1996 again. What I need to do, after making that select is this: --Capture the value in the selected cell --Scroll down, counting the rows until I find a different value --Scroll back up one row, and hold the adjusted count for number of rows --Scroll back to the right 6 columns to my "starting cell" --Invoke the Macro based on the number of rows counted ("Rows3" e.g.) The table has nearly 9,000 rows, and doing this junk manually I have reached row 800 in some two hours. If I could modify the macros to do this, it would do the whole shebang in a couple of heartbeats! All contributions gratefully received. Dave Temping with Staffmark in Rock Hill, SC |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extending a macro
Huge help there -- I had something I could hack into and try to tweak.
Selecting just the last dozen or so rows to try it on, my hack... Sub TryNow() Dim myCount As Integer Dim myCell As Range myCount = 0 For Each myCell In Range("E8593", Range("E65536").End(xlUp)) If myCell.Value < myCell(2, 1).Value Then myCell(1, 7).FormulaR1C1 = "=RC[-6]" myCell(1, 9).FormulaR1C1 = "=SUM(R[-" & myCount & "]C[-4]:RC[-4])" myCell(1, 11).FormulaR1C1 = "=SUM(R[-" & myCount & "]C[-5]:RC[-5])" myCount = 0 Else myCount = myCount + 1 End If Next myCell End Sub ....now places the copy of date column value correctly into its cell. However the calculations keep returning zeros, no motter how I tweak the negative numeric values in the SUM() expressions. How do I step through the macro and view the values as each line processes? -- Dave Temping with Staffmark in Rock Hill, SC "Bernie Deitrick" wrote: Dave, I thought that since your macro was using SUM you wanted sums.... Anyway, this macro assumes that your years are in column A, and you want your formulas in columns H, J, and L. (But you never described your actual layout.) It will do all the data at one time. Sub TryNow() Dim myCount As Integer Dim myCell As Range myCount = 0 For Each myCell In Range("A2", Range("A65536").End(xlUp)) If myCell.Value < myCell(2, 1).Value Then myCell(1, 8).FormulaR1C1 = "=RC[-6]" myCell(1, 10).FormulaR1C1 = "=SUM(R[-" & myCount & "]C[-6]:RC[-6])" myCell(1, 12).FormulaR1C1 = "=SUM(R[-" & myCount & "]C[-7]:RC[-7])" myCount = 0 Else myCount = myCount + 1 End If Next myCell End Sub HTH, Bernie MS Excel MVP "Dave Birley" wrote in message ... Sorry, but I'm a VFP and xBase programmer from way back (that's why I know what I would like <g) but this is the first time in my life I have done anything that looks like programming in Excel. I like the idea of "At each change in...", but when I entered the configuration dialog, I thought I could use Count -- pass the results to a new column created to receive them. Didn't work. Thank goodness for Ctrl-Z <G! -- Dave Temping with Staffmark in Rock Hill, SC "Bernie Deitrick" wrote: Dave, Try using the subtotal feature of Excel. Select your table, then use Data / Subtotals.... and pick your coulmn with year as the "At each change in.." column, and the sum your columns as desired. And if there are other columns of identifiers, consider using a Pivot table... HTH, Bernie MS Excel MVP "Dave Birley" wrote in message ... Here is a simple macro I am using: Sub Rows4() ' ' Rows4 Macro ' Macro recorded 4/20/2007 by Administrator ' ' ActiveCell.Select ActiveCell.FormulaR1C1 = "=RC[-6]" ActiveCell.Offset(0, 2).Range("A1").Select ActiveCell.FormulaR1C1 = "=SUM(R[-3]C[-6]:RC[-6])" ActiveCell.Offset(0, 2).Range("A1").Select ActiveCell.FormulaR1C1 = "=SUM(R[-3]C[-7]:RC[-7])" ActiveCell.Offset(1, -4).Range("A1").Select End Sub It is one of a family of 11 macros, each named "Rows[n]", and all I have to do is count how many rows I need to apply a macro to, and call the macro named for that number of rows with its hotkey. However, being fundamentally lazy (always give the hardest task to the laziest employee, and he/she will always find the easiest way to do it <g) -- I want to extend it a tiny bit. I would like to change that last line to: ActiveCell.Offset(1, -10).Range("A1").Select This column contains years, sequentially sorted. So there could be four 1996 then five 1997, then eleven 1998 then two 1996 again. What I need to do, after making that select is this: --Capture the value in the selected cell --Scroll down, counting the rows until I find a different value --Scroll back up one row, and hold the adjusted count for number of rows --Scroll back to the right 6 columns to my "starting cell" --Invoke the Macro based on the number of rows counted ("Rows3" e.g.) The table has nearly 9,000 rows, and doing this junk manually I have reached row 800 in some two hours. If I could modify the macros to do this, it would do the whole shebang in a couple of heartbeats! All contributions gratefully received. Dave Temping with Staffmark in Rock Hill, SC |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extending a macro
Never mind -- got it, those column values in the SUM() expresseions should
have been -6 and -7. All good and wonderful! Thanks a million. -- Dave Temping with Staffmark in Rock Hill, SC "Bernie Deitrick" wrote: Dave, I thought that since your macro was using SUM you wanted sums.... Anyway, this macro assumes that your years are in column A, and you want your formulas in columns H, J, and L. (But you never described your actual layout.) It will do all the data at one time. Sub TryNow() Dim myCount As Integer Dim myCell As Range myCount = 0 For Each myCell In Range("A2", Range("A65536").End(xlUp)) If myCell.Value < myCell(2, 1).Value Then myCell(1, 8).FormulaR1C1 = "=RC[-6]" myCell(1, 10).FormulaR1C1 = "=SUM(R[-" & myCount & "]C[-6]:RC[-6])" myCell(1, 12).FormulaR1C1 = "=SUM(R[-" & myCount & "]C[-7]:RC[-7])" myCount = 0 Else myCount = myCount + 1 End If Next myCell End Sub HTH, Bernie MS Excel MVP "Dave Birley" wrote in message ... Sorry, but I'm a VFP and xBase programmer from way back (that's why I know what I would like <g) but this is the first time in my life I have done anything that looks like programming in Excel. I like the idea of "At each change in...", but when I entered the configuration dialog, I thought I could use Count -- pass the results to a new column created to receive them. Didn't work. Thank goodness for Ctrl-Z <G! -- Dave Temping with Staffmark in Rock Hill, SC "Bernie Deitrick" wrote: Dave, Try using the subtotal feature of Excel. Select your table, then use Data / Subtotals.... and pick your coulmn with year as the "At each change in.." column, and the sum your columns as desired. And if there are other columns of identifiers, consider using a Pivot table... HTH, Bernie MS Excel MVP "Dave Birley" wrote in message ... Here is a simple macro I am using: Sub Rows4() ' ' Rows4 Macro ' Macro recorded 4/20/2007 by Administrator ' ' ActiveCell.Select ActiveCell.FormulaR1C1 = "=RC[-6]" ActiveCell.Offset(0, 2).Range("A1").Select ActiveCell.FormulaR1C1 = "=SUM(R[-3]C[-6]:RC[-6])" ActiveCell.Offset(0, 2).Range("A1").Select ActiveCell.FormulaR1C1 = "=SUM(R[-3]C[-7]:RC[-7])" ActiveCell.Offset(1, -4).Range("A1").Select End Sub It is one of a family of 11 macros, each named "Rows[n]", and all I have to do is count how many rows I need to apply a macro to, and call the macro named for that number of rows with its hotkey. However, being fundamentally lazy (always give the hardest task to the laziest employee, and he/she will always find the easiest way to do it <g) -- I want to extend it a tiny bit. I would like to change that last line to: ActiveCell.Offset(1, -10).Range("A1").Select This column contains years, sequentially sorted. So there could be four 1996 then five 1997, then eleven 1998 then two 1996 again. What I need to do, after making that select is this: --Capture the value in the selected cell --Scroll down, counting the rows until I find a different value --Scroll back up one row, and hold the adjusted count for number of rows --Scroll back to the right 6 columns to my "starting cell" --Invoke the Macro based on the number of rows counted ("Rows3" e.g.) The table has nearly 9,000 rows, and doing this junk manually I have reached row 800 in some two hours. If I could modify the macros to do this, it would do the whole shebang in a couple of heartbeats! All contributions gratefully received. Dave Temping with Staffmark in Rock Hill, SC |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extending a macro
Dave,
You're welcome. Glad to hear that you got it to work... Bernie MS Excel MVP All good and wonderful! Thanks a million. -- |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extending a macro
Now, just to get difficult, I decided to identify a surname, firstname pair
and do something similar: Sub TryToo() Dim myCell As Range For Each myCell In Range("E8546", Range("E65536").End(xlUp)) If myCell.Value < myCell(2, 1).Value Then myCell(1, 13).FormulaR1C1 = "=RC[-14] & ", " & RC[-13]" End If Next myCell End Sub If I run it with just "=RC[-14]", it delivers the surname value. However I get the error when I try to concatenate the first name field RC[-13]. Where am I going wrong, mother darling? {{Old English music hall song}} -- Dave Temping with Staffmark in Rock Hill, SC "Bernie Deitrick" wrote: Dave, You're welcome. Glad to hear that you got it to work... Bernie MS Excel MVP All good and wonderful! Thanks a million. -- |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extending a macro
Dave,
You need to double up internal diuble-quotes to get them to work: myCell(1, 13)..FormulaR1C1 = "=RC[-14] & "", "" & RC[-13]" The best way to code formulas is to get the formula working in a cell, then start the macro recorder, select the cell, press F2, press Enter, and stop the recorder. HTH, Bernie MS Excel MVP "Dave Birley" wrote in message ... Now, just to get difficult, I decided to identify a surname, firstname pair and do something similar: Sub TryToo() Dim myCell As Range For Each myCell In Range("E8546", Range("E65536").End(xlUp)) If myCell.Value < myCell(2, 1).Value Then myCell(1, 13).FormulaR1C1 = "=RC[-14] & ", " & RC[-13]" End If Next myCell End Sub If I run it with just "=RC[-14]", it delivers the surname value. However I get the error when I try to concatenate the first name field RC[-13]. Where am I going wrong, mother darling? {{Old English music hall song}} -- Dave Temping with Staffmark in Rock Hill, SC "Bernie Deitrick" wrote: Dave, You're welcome. Glad to hear that you got it to work... Bernie MS Excel MVP All good and wonderful! Thanks a million. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extending columns | New Users to Excel | |||
Extending VLOOKUP | Excel Worksheet Functions | |||
Extending Row() | Excel Worksheet Functions | |||
extending selection | Excel Discussion (Misc queries) | |||
extending dates | Excel Discussion (Misc queries) |