Extending a macro

[Guest]

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

 

[Bernie Deitrick]

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

 

[Guest]

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

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

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

 

[Bernie Deitrick]

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

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

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

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

 

[Guest]

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

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

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

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


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

 

[Guest]

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

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

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

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


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

 

[Bernie Deitrick]

Dave,

You're welcome. Glad to hear that you got it to work...

Bernie
MS Excel MVP

 

[Guest]

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}}

 

[Bernie Deitrick]

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

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

Dave,

You're welcome. Glad to hear that you got it to work...

Bernie
MS Excel MVP