how do I debug my Excel macro & make it actually WORK?

Here's the ever-so-simple task... use a macro to add two cells in a range,
all with numerical values - two crummy cells! And then get the result in a
new row (right under the cells in question) in bold text. 2 cells!! Wiith
simple numbers in them!!

Luckily they are in the same column in the same workbook! Whew... that
makes it sooo easy, don't it?

However... it has been a long time since I've used an Excel macro, and I
forgot how I solved this before. It took me hours of trial & error & futility
before, trying to use MS Help in Excel, online, and in Visual basic debugger,
etc. Eventually trial & error won out.

(can't microsoft just tell you how to fix this stuff when it doesn't work? -
a bunch of simple examples, maybe???? - nutty idea, a debugger that accept
requests in simple english, is interactive if necessary, and will solve your
problem in less than 6 hours??)

The problem is that when I use the macro, it assigns a fixed range for the
cells I want to add up. I can't run the macro again, because the darn thing
WON'T WORK (BYW, not shouting - using caps for emphasis)... here's the code:

Sub Macro7()
'
' Macro7 Macro
' Macro recorded 2/15/2006 by mark.diaz
'
' Keyboard Shortcut: Ctrl+z
'
Selection.EntireRow.Insert
Range("C24:C26").Select
Range("C26").Activate
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("C26").Select
Selection.Font.Bold = True
End Sub

As you can see the range for the cells is "fixed" - my term, because I don't
know what else to call it.

What I REALLY want to do is set set the cursor at the lower of the 2 (wild
thought, maybe even 3 or 4) cells, then hit Ctrl-z, and have the macro open a
new row witht he sum in bold text. That's the super simple macro.

Ideally, I would like to have a loop with 5 or 6 sets of 2-cell groups, and
have them all totaled up in bold.

is this too much to ask?

I have been working on this problem off and on since 11 AM, and it's now 5
PM. That's 6 hours for the numerically challenged.

I can't believe how difficult microsoft makes it to adapt their macros to do
the simplest tasks. I find it humiliating and frustrating to be defeated by
software that claims to be useful.

At any rate, if you can help me with this, I will be eternally grateful - or
at least until I try to write my next macro...

Mark
Boston, MA
direct: [email protected]

The following appears to do what I believe you want it to. Just select a
range (like b16:h16 for example, and then invoke the macro.)
Note: I did not change much from your original macro, and no error checking.
I believe I didn't really need to use myRange, I could have stayed with
selection.
ie with selection instead of with myrange.

Sub Macro7()
'
' Macro7 Macro
' Macro recorded 2/15/2006 by mark.diaz
'
' Keyboard Shortcut: Ctrl+z
'
Dim myRange As Range
Selection.EntireRow.Insert
Set myRange = ActiveSheet.Range(Selection.Address)
' ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
With myRange
.FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
.Font.Bold = True
End With



End Sub

--
Kevin Vaughn


"Brainless_in_Boston" wrote:

> Here's the ever-so-simple task... use a macro to add two cells in a range,
> all with numerical values - two crummy cells! And then get the result in a
> new row (right under the cells in question) in bold text. 2 cells!! Wiith
> simple numbers in them!!
>
> Luckily they are in the same column in the same workbook! Whew... that
> makes it sooo easy, don't it?
>
> However... it has been a long time since I've used an Excel macro, and I
> forgot how I solved this before. It took me hours of trial & error & futility
> before, trying to use MS Help in Excel, online, and in Visual basic debugger,
> etc. Eventually trial & error won out.
>
> (can't microsoft just tell you how to fix this stuff when it doesn't work? -
> a bunch of simple examples, maybe???? - nutty idea, a debugger that accept
> requests in simple english, is interactive if necessary, and will solve your
> problem in less than 6 hours??)
>
> The problem is that when I use the macro, it assigns a fixed range for the
> cells I want to add up. I can't run the macro again, because the darn thing
> WON'T WORK (BYW, not shouting - using caps for emphasis)... here's the code:
>
> Sub Macro7()
> '
> ' Macro7 Macro
> ' Macro recorded 2/15/2006 by mark.diaz
> '
> ' Keyboard Shortcut: Ctrl+z
> '
> Selection.EntireRow.Insert
> Range("C24:C26").Select
> Range("C26").Activate
> ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
> Range("C26").Select
> Selection.Font.Bold = True
> End Sub
>
> As you can see the range for the cells is "fixed" - my term, because I don't
> know what else to call it.
>
> What I REALLY want to do is set set the cursor at the lower of the 2 (wild
> thought, maybe even 3 or 4) cells, then hit Ctrl-z, and have the macro open a
> new row witht he sum in bold text. That's the super simple macro.
>
> Ideally, I would like to have a loop with 5 or 6 sets of 2-cell groups, and
> have them all totaled up in bold.
>
> is this too much to ask?
>
> I have been working on this problem off and on since 11 AM, and it's now 5
> PM. That's 6 hours for the numerically challenged.
>
> I can't believe how difficult microsoft makes it to adapt their macros to do
> the simplest tasks. I find it humiliating and frustrating to be defeated by
> software that claims to be useful.
>
> At any rate, if you can help me with this, I will be eternally grateful - or
> at least until I try to write my next macro...
>
> Mark
> Boston, MA
> direct: [email protected]
>

How do you know what to loop through in those 5 or 6 cells. Will they always be
the same column?

This just inserts a new row right where the active cell is. Then it puts that
formula in the cell:

Option Explicit
Sub testme()

Dim myCell As Range
Set myCell = ActiveCell

ActiveCell.EntireRow.Insert
With ActiveCell
.FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
.Font.Bold = True
End With

End Sub

ctrl-z is usually used for Edit|Undo. I'd stay away from the shortcut keys that
excel uses.

If you know the columns, this might get you closer:

Option Explicit
Sub testme()

Dim myRow As Long

ActiveCell.EntireRow.Insert

myRow = ActiveCell.Row
With ActiveSheet
With .Cells(myRow, "A")
.FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
.Font.Bold = True
End With
With .Cells(myRow, "d")
.FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
.Font.Bold = True
End With
With .Cells(myRow, "F")
.FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
.Font.Bold = True
End With
End With

End Sub


Brainless_in_Boston wrote:
>
> Here's the ever-so-simple task... use a macro to add two cells in a range,
> all with numerical values - two crummy cells! And then get the result in a
> new row (right under the cells in question) in bold text. 2 cells!! Wiith
> simple numbers in them!!
>
> Luckily they are in the same column in the same workbook! Whew... that
> makes it sooo easy, don't it?
>
> However... it has been a long time since I've used an Excel macro, and I
> forgot how I solved this before. It took me hours of trial & error & futility
> before, trying to use MS Help in Excel, online, and in Visual basic debugger,
> etc. Eventually trial & error won out.
>
> (can't microsoft just tell you how to fix this stuff when it doesn't work? -
> a bunch of simple examples, maybe???? - nutty idea, a debugger that accept
> requests in simple english, is interactive if necessary, and will solve your
> problem in less than 6 hours??)
>
> The problem is that when I use the macro, it assigns a fixed range for the
> cells I want to add up. I can't run the macro again, because the darn thing
> WON'T WORK (BYW, not shouting - using caps for emphasis)... here's the code:
>
> Sub Macro7()
> '
> ' Macro7 Macro
> ' Macro recorded 2/15/2006 by mark.diaz
> '
> ' Keyboard Shortcut: Ctrl+z
> '
> Selection.EntireRow.Insert
> Range("C24:C26").Select
> Range("C26").Activate
> ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
> Range("C26").Select
> Selection.Font.Bold = True
> End Sub
>
> As you can see the range for the cells is "fixed" - my term, because I don't
> know what else to call it.
>
> What I REALLY want to do is set set the cursor at the lower of the 2 (wild
> thought, maybe even 3 or 4) cells, then hit Ctrl-z, and have the macro open a
> new row witht he sum in bold text. That's the super simple macro.
>
> Ideally, I would like to have a loop with 5 or 6 sets of 2-cell groups, and
> have them all totaled up in bold.
>
> is this too much to ask?
>
> I have been working on this problem off and on since 11 AM, and it's now 5
> PM. That's 6 hours for the numerically challenged.
>
> I can't believe how difficult microsoft makes it to adapt their macros to do
> the simplest tasks. I find it humiliating and frustrating to be defeated by
> software that claims to be useful.
>
> At any rate, if you can help me with this, I will be eternally grateful - or
> at least until I try to write my next macro...
>
> Mark
> Boston, MA
> direct: [email protected]

--

Dave Peterson

Kevin -

This looks promising!

I'll try it and see if it works. I really appreciate your posting this for
me. Thank you!

Mark

"Kevin Vaughn" wrote:

> The following appears to do what I believe you want it to. Just select a
> range (like b16:h16 for example, and then invoke the macro.)
> Note: I did not change much from your original macro, and no error checking.
> I believe I didn't really need to use myRange, I could have stayed with
> selection.
> ie with selection instead of with myrange.
>
> Sub Macro7()
> '
> ' Macro7 Macro
> ' Macro recorded 2/15/2006 by mark.diaz
> '
> ' Keyboard Shortcut: Ctrl+z
> '
> Dim myRange As Range
> Selection.EntireRow.Insert
> Set myRange = ActiveSheet.Range(Selection.Address)
> ' ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
> With myRange
> .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> .Font.Bold = True
> End With
>
>
>
> End Sub
>
> --
> Kevin Vaughn
>
>
> "Brainless_in_Boston" wrote:
>
> > Here's the ever-so-simple task... use a macro to add two cells in a range,
> > all with numerical values - two crummy cells! And then get the result in a
> > new row (right under the cells in question) in bold text. 2 cells!! Wiith
> > simple numbers in them!!
> >
> > Luckily they are in the same column in the same workbook! Whew... that
> > makes it sooo easy, don't it?
> >
> > However... it has been a long time since I've used an Excel macro, and I
> > forgot how I solved this before. It took me hours of trial & error & futility
> > before, trying to use MS Help in Excel, online, and in Visual basic debugger,
> > etc. Eventually trial & error won out.
> >
> > (can't microsoft just tell you how to fix this stuff when it doesn't work? -
> > a bunch of simple examples, maybe???? - nutty idea, a debugger that accept
> > requests in simple english, is interactive if necessary, and will solve your
> > problem in less than 6 hours??)
> >
> > The problem is that when I use the macro, it assigns a fixed range for the
> > cells I want to add up. I can't run the macro again, because the darn thing
> > WON'T WORK (BYW, not shouting - using caps for emphasis)... here's the code:
> >
> > Sub Macro7()
> > '
> > ' Macro7 Macro
> > ' Macro recorded 2/15/2006 by mark.diaz
> > '
> > ' Keyboard Shortcut: Ctrl+z
> > '
> > Selection.EntireRow.Insert
> > Range("C24:C26").Select
> > Range("C26").Activate
> > ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
> > Range("C26").Select
> > Selection.Font.Bold = True
> > End Sub
> >
> > As you can see the range for the cells is "fixed" - my term, because I don't
> > know what else to call it.
> >
> > What I REALLY want to do is set set the cursor at the lower of the 2 (wild
> > thought, maybe even 3 or 4) cells, then hit Ctrl-z, and have the macro open a
> > new row witht he sum in bold text. That's the super simple macro.
> >
> > Ideally, I would like to have a loop with 5 or 6 sets of 2-cell groups, and
> > have them all totaled up in bold.
> >
> > is this too much to ask?
> >
> > I have been working on this problem off and on since 11 AM, and it's now 5
> > PM. That's 6 hours for the numerically challenged.
> >
> > I can't believe how difficult microsoft makes it to adapt their macros to do
> > the simplest tasks. I find it humiliating and frustrating to be defeated by
> > software that claims to be useful.
> >
> > At any rate, if you can help me with this, I will be eternally grateful - or
> > at least until I try to write my next macro...
> >
> > Mark
> > Boston, MA
> > direct: [email protected]
> >

Dave,

Thanks for your post, and your insightful question.

The task is a simple "add these cells task", and usually (but not always),
I'm only adding 2 cells together in a long column of numbers. I was thinking
that if I have a group of 5 or 6 sets of 2 cells, a short loop macro will
work.

For the purposes of this macro, the cells will always be in a single column.

Thanks for the code as well. It's interesting to see how different working
code is from the examples microsoft posts.

Not to rag on MS, but sheesh! I just think they are caught up in "greek
speek", and making Visual Basic too inaccessible to the average (i.e.: dumb
like me) person. I also think they way they try to teach newbies is either
too simple or too complicated. But that's me. Of course, once you learn
something, the basic stuff seems so easy, but getting there is not easy for
me.

They don't define things well enough for me to understand what needs to be
done to make an effective macro. Their basic tutorial listed Dim as a
command, but I had to look elsewhere to learn that Dim meant Dimension!

I'll try this code and see if it serves the purpose. Oh, and I'll avoid
Ctrl-z as a macro shortcut! Again, thanks.

Mark Diaz
Boston, MA

=============================================
"Dave Peterson" wrote:

> How do you know what to loop through in those 5 or 6 cells. Will they always be
> the same column?
>
> This just inserts a new row right where the active cell is. Then it puts that
> formula in the cell:
>
> Option Explicit
> Sub testme()
>
> Dim myCell As Range
> Set myCell = ActiveCell
>
> ActiveCell.EntireRow.Insert
> With ActiveCell
> .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> .Font.Bold = True
> End With
>
> End Sub
>
> ctrl-z is usually used for Edit|Undo. I'd stay away from the shortcut keys that
> excel uses.
>
> If you know the columns, this might get you closer:
>
> Option Explicit
> Sub testme()
>
> Dim myRow As Long
>
> ActiveCell.EntireRow.Insert
>
> myRow = ActiveCell.Row
> With ActiveSheet
> With .Cells(myRow, "A")
> .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> .Font.Bold = True
> End With
> With .Cells(myRow, "d")
> .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> .Font.Bold = True
> End With
> With .Cells(myRow, "F")
> .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> .Font.Bold = True
> End With
> End With
>
> End Sub
>
>
>

Kevin,

I tried the code, and got an odd result. When I ran the macro, I selected 4
cells with my cursor, C30-c33. I hit Ctrl-x, but it didn't work...

Here's what happened: 4 empty rows appeared abvoe the cells I selected, and
the cursor went to cell C17. Wow, talk about fun!!!!

any suggestions?

Mark
===================================================

"Kevin Vaughn" wrote:

> The following appears to do what I believe you want it to. Just select a
> range (like b16:h16 for example, and then invoke the macro.)
> Note: I did not change much from your original macro, and no error checking.
> I believe I didn't really need to use myRange, I could have stayed with
> selection.
> ie with selection instead of with myrange.
>
> Sub Macro7()
> '
> ' Macro7 Macro
> ' Macro recorded 2/15/2006 by mark.diaz
> '
> ' Keyboard Shortcut: Ctrl+z
> '
> Dim myRange As Range
> Selection.EntireRow.Insert
> Set myRange = ActiveSheet.Range(Selection.Address)
> ' ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
> With myRange
> .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> .Font.Bold = True
> End With
>
>
>
> End Sub
>
> --
> Kevin Vaughn
>
>
> "Brainless_in_Boston" wrote:
>
> > Here's the ever-so-simple task... use a macro to add two cells in a range,
> > all with numerical values - two crummy cells! And then get the result in a
> > new row (right under the cells in question) in bold text. 2 cells!! Wiith
> > simple numbers in them!!
> >
> > Luckily they are in the same column in the same workbook! Whew... that
> > makes it sooo easy, don't it?
> >
> > However... it has been a long time since I've used an Excel macro, and I
> > forgot how I solved this before. It took me hours of trial & error & futility
> > before, trying to use MS Help in Excel, online, and in Visual basic debugger,
> > etc. Eventually trial & error won out.
> >
> > (can't microsoft just tell you how to fix this stuff when it doesn't work? -
> > a bunch of simple examples, maybe???? - nutty idea, a debugger that accept
> > requests in simple english, is interactive if necessary, and will solve your
> > problem in less than 6 hours??)
> >
> > The problem is that when I use the macro, it assigns a fixed range for the
> > cells I want to add up. I can't run the macro again, because the darn thing
> > WON'T WORK (BYW, not shouting - using caps for emphasis)... here's the code:
> >
> > Sub Macro7()
> > '
> > ' Macro7 Macro
> > ' Macro recorded 2/15/2006 by mark.diaz
> > '
> > ' Keyboard Shortcut: Ctrl+z
> > '
> > Selection.EntireRow.Insert
> > Range("C24:C26").Select
> > Range("C26").Activate
> > ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
> > Range("C26").Select
> > Selection.Font.Bold = True
> > End Sub
> >
> > As you can see the range for the cells is "fixed" - my term, because I don't
> > know what else to call it.
> >
> > What I REALLY want to do is set set the cursor at the lower of the 2 (wild
> > thought, maybe even 3 or 4) cells, then hit Ctrl-z, and have the macro open a
> > new row witht he sum in bold text. That's the super simple macro.
> >
> > Ideally, I would like to have a loop with 5 or 6 sets of 2-cell groups, and
> > have them all totaled up in bold.
> >
> > is this too much to ask?
> >
> > I have been working on this problem off and on since 11 AM, and it's now 5
> > PM. That's 6 hours for the numerically challenged.
> >
> > I can't believe how difficult microsoft makes it to adapt their macros to do
> > the simplest tasks. I find it humiliating and frustrating to be defeated by
> > software that claims to be useful.
> >
> > At any rate, if you can help me with this, I will be eternally grateful - or
> > at least until I try to write my next macro...
> >
> > Mark
> > Boston, MA
> > direct: [email protected]
> >

Sorry, I found another anomaly -

when I select cells C30 & C31, hit Ctrl-x, I get 2 empty rows above C30, A
subtotal for cells C12 & C13 (not in BOLD), and the cursor rests at cell C17.

Can you suggest a fix?

Mark
===================================

"Kevin Vaughn" wrote:

> The following appears to do what I believe you want it to. Just select a
> range (like b16:h16 for example, and then invoke the macro.)
> Note: I did not change much from your original macro, and no error checking.
> I believe I didn't really need to use myRange, I could have stayed with
> selection.
> ie with selection instead of with myrange.
>
> Sub Macro7()
> '
> ' Macro7 Macro
> ' Macro recorded 2/15/2006 by mark.diaz
> '
> ' Keyboard Shortcut: Ctrl+z
> '
> Dim myRange As Range
> Selection.EntireRow.Insert
> Set myRange = ActiveSheet.Range(Selection.Address)
> ' ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
> With myRange
> .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> .Font.Bold = True
> End With
>
>
>
> End Sub
>
> --
> Kevin Vaughn
>
>
> "Brainless_in_Boston" wrote:
>
> > Here's the ever-so-simple task... use a macro to add two cells in a range,
> > all with numerical values - two crummy cells! And then get the result in a
> > new row (right under the cells in question) in bold text. 2 cells!! Wiith
> > simple numbers in them!!
> >
> > Luckily they are in the same column in the same workbook! Whew... that
> > makes it sooo easy, don't it?
> >
> > However... it has been a long time since I've used an Excel macro, and I
> > forgot how I solved this before. It took me hours of trial & error & futility
> > before, trying to use MS Help in Excel, online, and in Visual basic debugger,
> > etc. Eventually trial & error won out.
> >
> > (can't microsoft just tell you how to fix this stuff when it doesn't work? -
> > a bunch of simple examples, maybe???? - nutty idea, a debugger that accept
> > requests in simple english, is interactive if necessary, and will solve your
> > problem in less than 6 hours??)
> >
> > The problem is that when I use the macro, it assigns a fixed range for the
> > cells I want to add up. I can't run the macro again, because the darn thing
> > WON'T WORK (BYW, not shouting - using caps for emphasis)... here's the code:
> >
> > Sub Macro7()
> > '
> > ' Macro7 Macro
> > ' Macro recorded 2/15/2006 by mark.diaz
> > '
> > ' Keyboard Shortcut: Ctrl+z
> > '
> > Selection.EntireRow.Insert
> > Range("C24:C26").Select
> > Range("C26").Activate
> > ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
> > Range("C26").Select
> > Selection.Font.Bold = True
> > End Sub
> >
> > As you can see the range for the cells is "fixed" - my term, because I don't
> > know what else to call it.
> >
> > What I REALLY want to do is set set the cursor at the lower of the 2 (wild
> > thought, maybe even 3 or 4) cells, then hit Ctrl-z, and have the macro open a
> > new row witht he sum in bold text. That's the super simple macro.
> >
> > Ideally, I would like to have a loop with 5 or 6 sets of 2-cell groups, and
> > have them all totaled up in bold.
> >
> > is this too much to ask?
> >
> > I have been working on this problem off and on since 11 AM, and it's now 5
> > PM. That's 6 hours for the numerically challenged.
> >
> > I can't believe how difficult microsoft makes it to adapt their macros to do
> > the simplest tasks. I find it humiliating and frustrating to be defeated by
> > software that claims to be useful.
> >
> > At any rate, if you can help me with this, I will be eternally grateful - or
> > at least until I try to write my next macro...
> >
> > Mark
> > Boston, MA
> > direct: [email protected]
> >

Dave,

OK - here's what I used:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 2/16/2006 by mark.diaz
'
' Keyboard Shortcut: Ctrl+w
'

Dim myCell As Range
Set myCell = ActiveCell

ActiveCell.EntireRow.Insert
With ActiveCell
.FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
.Font.Bold = True
End With

End Sub

The macro totals the two cells, and inserts a line below them, with the
total in Bold. It then totals the bottom 2 cells again on a new row, and
bolds that as well.

Can you help? I'm unable to figure this out.

Mark
====================================

"Dave Peterson" wrote:

> How do you know what to loop through in those 5 or 6 cells. Will they always be
> the same column?
>
> This just inserts a new row right where the active cell is. Then it puts that
> formula in the cell:
>
> Option Explicit
> Sub testme()
>
> Dim myCell As Range
> Set myCell = ActiveCell
>
> ActiveCell.EntireRow.Insert
> With ActiveCell
> .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> .Font.Bold = True
> End With
>
> End Sub
>
> ctrl-z is usually used for Edit|Undo. I'd stay away from the shortcut keys that
> excel uses.
>
> If you know the columns, this might get you closer:
>
> Option Explicit
> Sub testme()
>
> Dim myRow As Long
>
> ActiveCell.EntireRow.Insert
>
> myRow = ActiveCell.Row
> With ActiveSheet
> With .Cells(myRow, "A")
> .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> .Font.Bold = True
> End With
> With .Cells(myRow, "d")
> .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> .Font.Bold = True
> End With
> With .Cells(myRow, "F")
> .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> .Font.Bold = True
> End With
> End With
>
> End Sub
>
>
> Brainless_in_Boston wrote:
> >
> > Here's the ever-so-simple task... use a macro to add two cells in a range,
> > all with numerical values - two crummy cells! And then get the result in a
> > new row (right under the cells in question) in bold text. 2 cells!! Wiith
> > simple numbers in them!!
> >
> > Luckily they are in the same column in the same workbook! Whew... that
> > makes it sooo easy, don't it?
> >
> > However... it has been a long time since I've used an Excel macro, and I
> > forgot how I solved this before. It took me hours of trial & error & futility
> > before, trying to use MS Help in Excel, online, and in Visual basic debugger,
> > etc. Eventually trial & error won out.
> >
> > (can't microsoft just tell you how to fix this stuff when it doesn't work? -
> > a bunch of simple examples, maybe???? - nutty idea, a debugger that accept
> > requests in simple english, is interactive if necessary, and will solve your
> > problem in less than 6 hours??)
> >
> > The problem is that when I use the macro, it assigns a fixed range for the
> > cells I want to add up. I can't run the macro again, because the darn thing
> > WON'T WORK (BYW, not shouting - using caps for emphasis)... here's the code:
> >
> > Sub Macro7()
> > '
> > ' Macro7 Macro
> > ' Macro recorded 2/15/2006 by mark.diaz
> > '
> > ' Keyboard Shortcut: Ctrl+z
> > '
> > Selection.EntireRow.Insert
> > Range("C24:C26").Select
> > Range("C26").Activate
> > ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
> > Range("C26").Select
> > Selection.Font.Bold = True
> > End Sub
> >
> > As you can see the range for the cells is "fixed" - my term, because I don't
> > know what else to call it.
> >
> > What I REALLY want to do is set set the cursor at the lower of the 2 (wild
> > thought, maybe even 3 or 4) cells, then hit Ctrl-z, and have the macro open a
> > new row witht he sum in bold text. That's the super simple macro.
> >
> > Ideally, I would like to have a loop with 5 or 6 sets of 2-cell groups, and
> > have them all totaled up in bold.
> >
> > is this too much to ask?
> >
> > I have been working on this problem off and on since 11 AM, and it's now 5
> > PM. That's 6 hours for the numerically challenged.
> >
> > I can't believe how difficult microsoft makes it to adapt their macros to do
> > the simplest tasks. I find it humiliating and frustrating to be defeated by
> > software that claims to be useful.
> >
> > At any rate, if you can help me with this, I will be eternally grateful - or
> > at least until I try to write my next macro...
> >
> > Mark
> > Boston, MA
> > direct: [email protected]
>
> --
>
> Dave Peterson
>

I only tested it with 1 row selected (possibly multiple columns.) Also,
based on your other post, I should point out that you want to be on the row
below where the last number that you want to total is. ie, if you want to
total c30 and c31, you should be on c32 when you invoke the macro. This may
not have been what you intended, but based on my first run-through of the
program, it seemed the most likely to me.
--
Kevin Vaughn


"Brainless_in_Boston" wrote:

> Sorry, I found another anomaly -
>
> when I select cells C30 & C31, hit Ctrl-x, I get 2 empty rows above C30, A
> subtotal for cells C12 & C13 (not in BOLD), and the cursor rests at cell C17.
>
> Can you suggest a fix?
>
> Mark
> ===================================
>
> "Kevin Vaughn" wrote:
>
> > The following appears to do what I believe you want it to. Just select a
> > range (like b16:h16 for example, and then invoke the macro.)
> > Note: I did not change much from your original macro, and no error checking.
> > I believe I didn't really need to use myRange, I could have stayed with
> > selection.
> > ie with selection instead of with myrange.
> >
> > Sub Macro7()
> > '
> > ' Macro7 Macro
> > ' Macro recorded 2/15/2006 by mark.diaz
> > '
> > ' Keyboard Shortcut: Ctrl+z
> > '
> > Dim myRange As Range
> > Selection.EntireRow.Insert
> > Set myRange = ActiveSheet.Range(Selection.Address)
> > ' ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
> > With myRange
> > .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> > .Font.Bold = True
> > End With
> >
> >
> >
> > End Sub
> >
> > --
> > Kevin Vaughn
> >
> >
> > "Brainless_in_Boston" wrote:
> >
> > > Here's the ever-so-simple task... use a macro to add two cells in a range,
> > > all with numerical values - two crummy cells! And then get the result in a
> > > new row (right under the cells in question) in bold text. 2 cells!! Wiith
> > > simple numbers in them!!
> > >
> > > Luckily they are in the same column in the same workbook! Whew... that
> > > makes it sooo easy, don't it?
> > >
> > > However... it has been a long time since I've used an Excel macro, and I
> > > forgot how I solved this before. It took me hours of trial & error & futility
> > > before, trying to use MS Help in Excel, online, and in Visual basic debugger,
> > > etc. Eventually trial & error won out.
> > >
> > > (can't microsoft just tell you how to fix this stuff when it doesn't work? -
> > > a bunch of simple examples, maybe???? - nutty idea, a debugger that accept
> > > requests in simple english, is interactive if necessary, and will solve your
> > > problem in less than 6 hours??)
> > >
> > > The problem is that when I use the macro, it assigns a fixed range for the
> > > cells I want to add up. I can't run the macro again, because the darn thing
> > > WON'T WORK (BYW, not shouting - using caps for emphasis)... here's the code:
> > >
> > > Sub Macro7()
> > > '
> > > ' Macro7 Macro
> > > ' Macro recorded 2/15/2006 by mark.diaz
> > > '
> > > ' Keyboard Shortcut: Ctrl+z
> > > '
> > > Selection.EntireRow.Insert
> > > Range("C24:C26").Select
> > > Range("C26").Activate
> > > ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
> > > Range("C26").Select
> > > Selection.Font.Bold = True
> > > End Sub
> > >
> > > As you can see the range for the cells is "fixed" - my term, because I don't
> > > know what else to call it.
> > >
> > > What I REALLY want to do is set set the cursor at the lower of the 2 (wild
> > > thought, maybe even 3 or 4) cells, then hit Ctrl-z, and have the macro open a
> > > new row witht he sum in bold text. That's the super simple macro.
> > >
> > > Ideally, I would like to have a loop with 5 or 6 sets of 2-cell groups, and
> > > have them all totaled up in bold.
> > >
> > > is this too much to ask?
> > >
> > > I have been working on this problem off and on since 11 AM, and it's now 5
> > > PM. That's 6 hours for the numerically challenged.
> > >
> > > I can't believe how difficult microsoft makes it to adapt their macros to do
> > > the simplest tasks. I find it humiliating and frustrating to be defeated by
> > > software that claims to be useful.
> > >
> > > At any rate, if you can help me with this, I will be eternally grateful - or
> > > at least until I try to write my next macro...
> > >
> > > Mark
> > > Boston, MA
> > > direct: [email protected]
> > >

Ah, I see what you are intending. You select the rows you want totaled and
then run the macro. Let me see if I can modify it to do this.
--
Kevin Vaughn


"Brainless_in_Boston" wrote:

> Kevin,
>
> I tried the code, and got an odd result. When I ran the macro, I selected 4
> cells with my cursor, C30-c33. I hit Ctrl-x, but it didn't work...
>
> Here's what happened: 4 empty rows appeared abvoe the cells I selected, and
> the cursor went to cell C17. Wow, talk about fun!!!!
>
> any suggestions?
>
> Mark
> ===================================================
>
> "Kevin Vaughn" wrote:
>
> > The following appears to do what I believe you want it to. Just select a
> > range (like b16:h16 for example, and then invoke the macro.)
> > Note: I did not change much from your original macro, and no error checking.
> > I believe I didn't really need to use myRange, I could have stayed with
> > selection.
> > ie with selection instead of with myrange.
> >
> > Sub Macro7()
> > '
> > ' Macro7 Macro
> > ' Macro recorded 2/15/2006 by mark.diaz
> > '
> > ' Keyboard Shortcut: Ctrl+z
> > '
> > Dim myRange As Range
> > Selection.EntireRow.Insert
> > Set myRange = ActiveSheet.Range(Selection.Address)
> > ' ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
> > With myRange
> > .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> > .Font.Bold = True
> > End With
> >
> >
> >
> > End Sub
> >
> > --
> > Kevin Vaughn
> >
> >
> > "Brainless_in_Boston" wrote:
> >
> > > Here's the ever-so-simple task... use a macro to add two cells in a range,
> > > all with numerical values - two crummy cells! And then get the result in a
> > > new row (right under the cells in question) in bold text. 2 cells!! Wiith
> > > simple numbers in them!!
> > >
> > > Luckily they are in the same column in the same workbook! Whew... that
> > > makes it sooo easy, don't it?
> > >
> > > However... it has been a long time since I've used an Excel macro, and I
> > > forgot how I solved this before. It took me hours of trial & error & futility
> > > before, trying to use MS Help in Excel, online, and in Visual basic debugger,
> > > etc. Eventually trial & error won out.
> > >
> > > (can't microsoft just tell you how to fix this stuff when it doesn't work? -
> > > a bunch of simple examples, maybe???? - nutty idea, a debugger that accept
> > > requests in simple english, is interactive if necessary, and will solve your
> > > problem in less than 6 hours??)
> > >
> > > The problem is that when I use the macro, it assigns a fixed range for the
> > > cells I want to add up. I can't run the macro again, because the darn thing
> > > WON'T WORK (BYW, not shouting - using caps for emphasis)... here's the code:
> > >
> > > Sub Macro7()
> > > '
> > > ' Macro7 Macro
> > > ' Macro recorded 2/15/2006 by mark.diaz
> > > '
> > > ' Keyboard Shortcut: Ctrl+z
> > > '
> > > Selection.EntireRow.Insert
> > > Range("C24:C26").Select
> > > Range("C26").Activate
> > > ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
> > > Range("C26").Select
> > > Selection.Font.Bold = True
> > > End Sub
> > >
> > > As you can see the range for the cells is "fixed" - my term, because I don't
> > > know what else to call it.
> > >
> > > What I REALLY want to do is set set the cursor at the lower of the 2 (wild
> > > thought, maybe even 3 or 4) cells, then hit Ctrl-z, and have the macro open a
> > > new row witht he sum in bold text. That's the super simple macro.
> > >
> > > Ideally, I would like to have a loop with 5 or 6 sets of 2-cell groups, and
> > > have them all totaled up in bold.
> > >
> > > is this too much to ask?
> > >
> > > I have been working on this problem off and on since 11 AM, and it's now 5
> > > PM. That's 6 hours for the numerically challenged.
> > >
> > > I can't believe how difficult microsoft makes it to adapt their macros to do
> > > the simplest tasks. I find it humiliating and frustrating to be defeated by
> > > software that claims to be useful.
> > >
> > > At any rate, if you can help me with this, I will be eternally grateful - or
> > > at least until I try to write my next macro...
> > >
> > > Mark
> > > Boston, MA
> > > direct: [email protected]
> > >

If that's the code you used, I'd guess you hit ctrl-w too quickly--and ran it
twice.

Brainless_in_Boston wrote:
>
> Dave,
>
> OK - here's what I used:
>
> Sub Macro2()
> '
> ' Macro2 Macro
> ' Macro recorded 2/16/2006 by mark.diaz
> '
> ' Keyboard Shortcut: Ctrl+w
> '
>
> Dim myCell As Range
> Set myCell = ActiveCell
>
> ActiveCell.EntireRow.Insert
> With ActiveCell
> .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> .Font.Bold = True
> End With
>
> End Sub
>
> The macro totals the two cells, and inserts a line below them, with the
> total in Bold. It then totals the bottom 2 cells again on a new row, and
> bolds that as well.
>
> Can you help? I'm unable to figure this out.
>
> Mark
> ====================================
>
> "Dave Peterson" wrote:
>
> > How do you know what to loop through in those 5 or 6 cells. Will they always be
> > the same column?
> >
> > This just inserts a new row right where the active cell is. Then it puts that
> > formula in the cell:
> >
> > Option Explicit
> > Sub testme()
> >
> > Dim myCell As Range
> > Set myCell = ActiveCell
> >
> > ActiveCell.EntireRow.Insert
> > With ActiveCell
> > .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> > .Font.Bold = True
> > End With
> >
> > End Sub
> >
> > ctrl-z is usually used for Edit|Undo. I'd stay away from the shortcut keys that
> > excel uses.
> >
> > If you know the columns, this might get you closer:
> >
> > Option Explicit
> > Sub testme()
> >
> > Dim myRow As Long
> >
> > ActiveCell.EntireRow.Insert
> >
> > myRow = ActiveCell.Row
> > With ActiveSheet
> > With .Cells(myRow, "A")
> > .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> > .Font.Bold = True
> > End With
> > With .Cells(myRow, "d")
> > .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> > .Font.Bold = True
> > End With
> > With .Cells(myRow, "F")
> > .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> > .Font.Bold = True
> > End With
> > End With
> >
> > End Sub
> >
> >
> > Brainless_in_Boston wrote:
> > >
> > > Here's the ever-so-simple task... use a macro to add two cells in a range,
> > > all with numerical values - two crummy cells! And then get the result in a
> > > new row (right under the cells in question) in bold text. 2 cells!! Wiith
> > > simple numbers in them!!
> > >
> > > Luckily they are in the same column in the same workbook! Whew... that
> > > makes it sooo easy, don't it?
> > >
> > > However... it has been a long time since I've used an Excel macro, and I
> > > forgot how I solved this before. It took me hours of trial & error & futility
> > > before, trying to use MS Help in Excel, online, and in Visual basic debugger,
> > > etc. Eventually trial & error won out.
> > >
> > > (can't microsoft just tell you how to fix this stuff when it doesn't work? -
> > > a bunch of simple examples, maybe???? - nutty idea, a debugger that accept
> > > requests in simple english, is interactive if necessary, and will solve your
> > > problem in less than 6 hours??)
> > >
> > > The problem is that when I use the macro, it assigns a fixed range for the
> > > cells I want to add up. I can't run the macro again, because the darn thing
> > > WON'T WORK (BYW, not shouting - using caps for emphasis)... here's the code:
> > >
> > > Sub Macro7()
> > > '
> > > ' Macro7 Macro
> > > ' Macro recorded 2/15/2006 by mark.diaz
> > > '
> > > ' Keyboard Shortcut: Ctrl+z
> > > '
> > > Selection.EntireRow.Insert
> > > Range("C24:C26").Select
> > > Range("C26").Activate
> > > ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
> > > Range("C26").Select
> > > Selection.Font.Bold = True
> > > End Sub
> > >
> > > As you can see the range for the cells is "fixed" - my term, because I don't
> > > know what else to call it.
> > >
> > > What I REALLY want to do is set set the cursor at the lower of the 2 (wild
> > > thought, maybe even 3 or 4) cells, then hit Ctrl-z, and have the macro open a
> > > new row witht he sum in bold text. That's the super simple macro.
> > >
> > > Ideally, I would like to have a loop with 5 or 6 sets of 2-cell groups, and
> > > have them all totaled up in bold.
> > >
> > > is this too much to ask?
> > >
> > > I have been working on this problem off and on since 11 AM, and it's now 5
> > > PM. That's 6 hours for the numerically challenged.
> > >
> > > I can't believe how difficult microsoft makes it to adapt their macros to do
> > > the simplest tasks. I find it humiliating and frustrating to be defeated by
> > > software that claims to be useful.
> > >
> > > At any rate, if you can help me with this, I will be eternally grateful - or
> > > at least until I try to write my next macro...
> > >
> > > Mark
> > > Boston, MA
> > > direct: [email protected]
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Dave,

I think I got it. The extra line between:

Set myCell = ActiveCell
(and)
ActiveCell.EntireRow.Insert

Was causing the problem. Can you suggest how to have a variable number of
cells in one column, and get the total in bold on a new line?

Can i use:

.FormulaR1C1 = "=sum(r[-x]c:r[-1]c)"

where (please give me an example) x is the top cell in the column I am
totalling?

I don't have a clue as to how VB handles variables like this. Any advice is
appreciated. Thanks.






"Dave Peterson" wrote:

> How do you know what to loop through in those 5 or 6 cells. Will they always be
> the same column?
>
> This just inserts a new row right where the active cell is. Then it puts that
> formula in the cell:
>
> Option Explicit
> Sub testme()
>
> Dim myCell As Range
> Set myCell = ActiveCell
>
> ActiveCell.EntireRow.Insert
> With ActiveCell
> .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> .Font.Bold = True
> End With
>
> End Sub
>
> ctrl-z is usually used for Edit|Undo. I'd stay away from the shortcut keys that
> excel uses.
>
> If you know the columns, this might get you closer:
>
> Option Explicit
> Sub testme()
>
> Dim myRow As Long
>
> ActiveCell.EntireRow.Insert
>
> myRow = ActiveCell.Row
> With ActiveSheet
> With .Cells(myRow, "A")
> .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> .Font.Bold = True
> End With
> With .Cells(myRow, "d")
> .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> .Font.Bold = True
> End With
> With .Cells(myRow, "F")
> .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> .Font.Bold = True
> End With
> End With
>
> End Sub
>
>
> Brainless_in_Boston wrote:
> >
> > Here's the ever-so-simple task... use a macro to add two cells in a range,
> > all with numerical values - two crummy cells! And then get the result in a
> > new row (right under the cells in question) in bold text. 2 cells!! Wiith
> > simple numbers in them!!
> >
> > Luckily they are in the same column in the same workbook! Whew... that
> > makes it sooo easy, don't it?
> >
> > However... it has been a long time since I've used an Excel macro, and I
> > forgot how I solved this before. It took me hours of trial & error & futility
> > before, trying to use MS Help in Excel, online, and in Visual basic debugger,
> > etc. Eventually trial & error won out.
> >
> > (can't microsoft just tell you how to fix this stuff when it doesn't work? -
> > a bunch of simple examples, maybe???? - nutty idea, a debugger that accept
> > requests in simple english, is interactive if necessary, and will solve your
> > problem in less than 6 hours??)
> >
> > The problem is that when I use the macro, it assigns a fixed range for the
> > cells I want to add up. I can't run the macro again, because the darn thing
> > WON'T WORK (BYW, not shouting - using caps for emphasis)... here's the code:
> >
> > Sub Macro7()
> > '
> > ' Macro7 Macro
> > ' Macro recorded 2/15/2006 by mark.diaz
> > '
> > ' Keyboard Shortcut: Ctrl+z
> > '
> > Selection.EntireRow.Insert
> > Range("C24:C26").Select
> > Range("C26").Activate
> > ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
> > Range("C26").Select
> > Selection.Font.Bold = True
> > End Sub
> >
> > As you can see the range for the cells is "fixed" - my term, because I don't
> > know what else to call it.
> >
> > What I REALLY want to do is set set the cursor at the lower of the 2 (wild
> > thought, maybe even 3 or 4) cells, then hit Ctrl-z, and have the macro open a
> > new row witht he sum in bold text. That's the super simple macro.
> >
> > Ideally, I would like to have a loop with 5 or 6 sets of 2-cell groups, and
> > have them all totaled up in bold.
> >
> > is this too much to ask?
> >
> > I have been working on this problem off and on since 11 AM, and it's now 5
> > PM. That's 6 hours for the numerically challenged.
> >
> > I can't believe how difficult microsoft makes it to adapt their macros to do
> > the simplest tasks. I find it humiliating and frustrating to be defeated by
> > software that claims to be useful.
> >
> > At any rate, if you can help me with this, I will be eternally grateful - or
> > at least until I try to write my next macro...
> >
> > Mark
> > Boston, MA
> > direct: [email protected]
>
> --
>
> Dave Peterson
>

Here is the new version. You can select multiple columns or a single column,
appears to work either way. Select the rows you want to total and then
invoke the macro and it will add the formula and bold it.

Sub Macro7()
Dim myRange As Range
Dim RowCount As Long
RowCount = Selection.Rows.Count
Set myRange = ActiveSheet.Range(Selection.Address)
' ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
myRange.Offset(RowCount).Resize(1).EntireRow.Insert
With myRange.Offset(RowCount).Resize(1)
.FormulaR1C1 = "=sum(r[-" & RowCount & "]c:r[-1]c)"
.Font.Bold = True
End With
End Sub

--
Kevin Vaughn


"Kevin Vaughn" wrote:

> Ah, I see what you are intending. You select the rows you want totaled and
> then run the macro. Let me see if I can modify it to do this.
> --
> Kevin Vaughn
>
>
> "Brainless_in_Boston" wrote:
>
> > Kevin,
> >
> > I tried the code, and got an odd result. When I ran the macro, I selected 4
> > cells with my cursor, C30-c33. I hit Ctrl-x, but it didn't work...
> >
> > Here's what happened: 4 empty rows appeared abvoe the cells I selected, and
> > the cursor went to cell C17. Wow, talk about fun!!!!
> >
> > any suggestions?
> >
> > Mark
> > ===================================================
> >
> > "Kevin Vaughn" wrote:
> >
> > > The following appears to do what I believe you want it to. Just select a
> > > range (like b16:h16 for example, and then invoke the macro.)
> > > Note: I did not change much from your original macro, and no error checking.
> > > I believe I didn't really need to use myRange, I could have stayed with
> > > selection.
> > > ie with selection instead of with myrange.
> > >
> > > Sub Macro7()
> > > '
> > > ' Macro7 Macro
> > > ' Macro recorded 2/15/2006 by mark.diaz
> > > '
> > > ' Keyboard Shortcut: Ctrl+z
> > > '
> > > Dim myRange As Range
> > > Selection.EntireRow.Insert
> > > Set myRange = ActiveSheet.Range(Selection.Address)
> > > ' ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
> > > With myRange
> > > .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> > > .Font.Bold = True
> > > End With
> > >
> > >
> > >
> > > End Sub
> > >
> > > --
> > > Kevin Vaughn
> > >
> > >
> > > "Brainless_in_Boston" wrote:
> > >
> > > > Here's the ever-so-simple task... use a macro to add two cells in a range,
> > > > all with numerical values - two crummy cells! And then get the result in a
> > > > new row (right under the cells in question) in bold text. 2 cells!! Wiith
> > > > simple numbers in them!!
> > > >
> > > > Luckily they are in the same column in the same workbook! Whew... that
> > > > makes it sooo easy, don't it?
> > > >
> > > > However... it has been a long time since I've used an Excel macro, and I
> > > > forgot how I solved this before. It took me hours of trial & error & futility
> > > > before, trying to use MS Help in Excel, online, and in Visual basic debugger,
> > > > etc. Eventually trial & error won out.
> > > >
> > > > (can't microsoft just tell you how to fix this stuff when it doesn't work? -
> > > > a bunch of simple examples, maybe???? - nutty idea, a debugger that accept
> > > > requests in simple english, is interactive if necessary, and will solve your
> > > > problem in less than 6 hours??)
> > > >
> > > > The problem is that when I use the macro, it assigns a fixed range for the
> > > > cells I want to add up. I can't run the macro again, because the darn thing
> > > > WON'T WORK (BYW, not shouting - using caps for emphasis)... here's the code:
> > > >
> > > > Sub Macro7()
> > > > '
> > > > ' Macro7 Macro
> > > > ' Macro recorded 2/15/2006 by mark.diaz
> > > > '
> > > > ' Keyboard Shortcut: Ctrl+z
> > > > '
> > > > Selection.EntireRow.Insert
> > > > Range("C24:C26").Select
> > > > Range("C26").Activate
> > > > ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
> > > > Range("C26").Select
> > > > Selection.Font.Bold = True
> > > > End Sub
> > > >
> > > > As you can see the range for the cells is "fixed" - my term, because I don't
> > > > know what else to call it.
> > > >
> > > > What I REALLY want to do is set set the cursor at the lower of the 2 (wild
> > > > thought, maybe even 3 or 4) cells, then hit Ctrl-z, and have the macro open a
> > > > new row witht he sum in bold text. That's the super simple macro.
> > > >
> > > > Ideally, I would like to have a loop with 5 or 6 sets of 2-cell groups, and
> > > > have them all totaled up in bold.
> > > >
> > > > is this too much to ask?
> > > >
> > > > I have been working on this problem off and on since 11 AM, and it's now 5
> > > > PM. That's 6 hours for the numerically challenged.
> > > >
> > > > I can't believe how difficult microsoft makes it to adapt their macros to do
> > > > the simplest tasks. I find it humiliating and frustrating to be defeated by
> > > > software that claims to be useful.
> > > >
> > > > At any rate, if you can help me with this, I will be eternally grateful - or
> > > > at least until I try to write my next macro...
> > > >
> > > > Mark
> > > > Boston, MA
> > > > direct: [email protected]
> > > >

Kevin,

This code looks great. Thanks for your post. I'm dying to test it - as soon
as I finish my reconciliation here.

I looked for an online glossary that would explain code/commands, and how to
assemble functional VB code, but I certainly did not find anything that
included "As Range" and such. I looked, believe me.

Can you suggest any free sources for a beginner like me? I spent a lot of
time on this yesterday, and got nowhere.

Again thanks for your expertise. I really appreciate it.

Mark
=========================================

"Kevin Vaughn" wrote:

> Here is the new version. You can select multiple columns or a single column,
> appears to work either way. Select the rows you want to total and then
> invoke the macro and it will add the formula and bold it.
>
> Sub Macro7()
> Dim myRange As Range
> Dim RowCount As Long
> RowCount = Selection.Rows.Count
> Set myRange = ActiveSheet.Range(Selection.Address)
> ' ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
> myRange.Offset(RowCount).Resize(1).EntireRow.Insert
> With myRange.Offset(RowCount).Resize(1)
> .FormulaR1C1 = "=sum(r[-" & RowCount & "]c:r[-1]c)"
> .Font.Bold = True
> End With
> End Sub
>
> --
> Kevin Vaughn
>
>
> "Kevin Vaughn" wrote:
>
> > Ah, I see what you are intending. You select the rows you want totaled and
> > then run the macro. Let me see if I can modify it to do this.
> > --
> > Kevin Vaughn
> >

I don't have any online references for you per se. A lot of the MVPs have
their own web sites which you might want to check out. I have quite a number
but I don't have them delineated as to what they cover (i.e. programming or
other.) Most of what I have learned has been from books. I can recommend
John Walkenbach. For instance, Excell 2002 Power Programming with VBA (I am
actually using 2000 and I know 2003 is out, but that was the book that was
available at my bookstore when I went shopping.)

If you get no further responses as to online resources for programming you
might want to either post a new question or maybe try a search to see what
has been recommended in the past.

--
Kevin Vaughn


"Brainless_in_Boston" wrote:

> Kevin,
>
> This code looks great. Thanks for your post. I'm dying to test it - as soon
> as I finish my reconciliation here.
>
> I looked for an online glossary that would explain code/commands, and how to
> assemble functional VB code, but I certainly did not find anything that
> included "As Range" and such. I looked, believe me.
>
> Can you suggest any free sources for a beginner like me? I spent a lot of
> time on this yesterday, and got nowhere.
>
> Again thanks for your expertise. I really appreciate it.
>
> Mark
> =========================================
>
> "Kevin Vaughn" wrote:
>
> > Here is the new version. You can select multiple columns or a single column,
> > appears to work either way. Select the rows you want to total and then
> > invoke the macro and it will add the formula and bold it.
> >
> > Sub Macro7()
> > Dim myRange As Range
> > Dim RowCount As Long
> > RowCount = Selection.Rows.Count
> > Set myRange = ActiveSheet.Range(Selection.Address)
> > ' ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
> > myRange.Offset(RowCount).Resize(1).EntireRow.Insert
> > With myRange.Offset(RowCount).Resize(1)
> > .FormulaR1C1 = "=sum(r[-" & RowCount & "]c:r[-1]c)"
> > .Font.Bold = True
> > End With
> > End Sub
> >
> > --
> > Kevin Vaughn
> >
> >
> > "Kevin Vaughn" wrote:
> >
> > > Ah, I see what you are intending. You select the rows you want totaled and
> > > then run the macro. Let me see if I can modify it to do this.
> > > --
> > > Kevin Vaughn
> > >

In fact, those top two lines don't do anything:

Dim myCell As Range
Set myCell = ActiveCell

Just delete them. (I was gonna use a variable, but changed my mind and forgot
to delete them--but they shouldn't have caused any trouble, either.)

And you want the top row of the column? It'll be row 1:

..FormulaR1C1 = "=sum(r1c:r[-1]c)"

Brainless_in_Boston wrote:
>
> Dave,
>
> I think I got it. The extra line between:
>
> Set myCell = ActiveCell
> (and)
> ActiveCell.EntireRow.Insert
>
> Was causing the problem. Can you suggest how to have a variable number of
> cells in one column, and get the total in bold on a new line?
>
> Can i use:
>
> .FormulaR1C1 = "=sum(r[-x]c:r[-1]c)"
>
> where (please give me an example) x is the top cell in the column I am
> totalling?
>
> I don't have a clue as to how VB handles variables like this. Any advice is
> appreciated. Thanks.
>
>
>
> "Dave Peterson" wrote:
>
> > How do you know what to loop through in those 5 or 6 cells. Will they always be
> > the same column?
> >
> > This just inserts a new row right where the active cell is. Then it puts that
> > formula in the cell:
> >
> > Option Explicit
> > Sub testme()
> >
> > Dim myCell As Range
> > Set myCell = ActiveCell
> >
> > ActiveCell.EntireRow.Insert
> > With ActiveCell
> > .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> > .Font.Bold = True
> > End With
> >
> > End Sub
> >
> > ctrl-z is usually used for Edit|Undo. I'd stay away from the shortcut keys that
> > excel uses.
> >
> > If you know the columns, this might get you closer:
> >
> > Option Explicit
> > Sub testme()
> >
> > Dim myRow As Long
> >
> > ActiveCell.EntireRow.Insert
> >
> > myRow = ActiveCell.Row
> > With ActiveSheet
> > With .Cells(myRow, "A")
> > .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> > .Font.Bold = True
> > End With
> > With .Cells(myRow, "d")
> > .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> > .Font.Bold = True
> > End With
> > With .Cells(myRow, "F")
> > .FormulaR1C1 = "=sum(r[-2]c:r[-1]c)"
> > .Font.Bold = True
> > End With
> > End With
> >
> > End Sub
> >
> >
> > Brainless_in_Boston wrote:
> > >
> > > Here's the ever-so-simple task... use a macro to add two cells in a range,
> > > all with numerical values - two crummy cells! And then get the result in a
> > > new row (right under the cells in question) in bold text. 2 cells!! Wiith
> > > simple numbers in them!!
> > >
> > > Luckily they are in the same column in the same workbook! Whew... that
> > > makes it sooo easy, don't it?
> > >
> > > However... it has been a long time since I've used an Excel macro, and I
> > > forgot how I solved this before. It took me hours of trial & error & futility
> > > before, trying to use MS Help in Excel, online, and in Visual basic debugger,
> > > etc. Eventually trial & error won out.
> > >
> > > (can't microsoft just tell you how to fix this stuff when it doesn't work? -
> > > a bunch of simple examples, maybe???? - nutty idea, a debugger that accept
> > > requests in simple english, is interactive if necessary, and will solve your
> > > problem in less than 6 hours??)
> > >
> > > The problem is that when I use the macro, it assigns a fixed range for the
> > > cells I want to add up. I can't run the macro again, because the darn thing
> > > WON'T WORK (BYW, not shouting - using caps for emphasis)... here's the code:
> > >
> > > Sub Macro7()
> > > '
> > > ' Macro7 Macro
> > > ' Macro recorded 2/15/2006 by mark.diaz
> > > '
> > > ' Keyboard Shortcut: Ctrl+z
> > > '
> > > Selection.EntireRow.Insert
> > > Range("C24:C26").Select
> > > Range("C26").Activate
> > > ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
> > > Range("C26").Select
> > > Selection.Font.Bold = True
> > > End Sub
> > >
> > > As you can see the range for the cells is "fixed" - my term, because I don't
> > > know what else to call it.
> > >
> > > What I REALLY want to do is set set the cursor at the lower of the 2 (wild
> > > thought, maybe even 3 or 4) cells, then hit Ctrl-z, and have the macro open a
> > > new row witht he sum in bold text. That's the super simple macro.
> > >
> > > Ideally, I would like to have a loop with 5 or 6 sets of 2-cell groups, and
> > > have them all totaled up in bold.
> > >
> > > is this too much to ask?
> > >
> > > I have been working on this problem off and on since 11 AM, and it's now 5
> > > PM. That's 6 hours for the numerically challenged.
> > >
> > > I can't believe how difficult microsoft makes it to adapt their macros to do
> > > the simplest tasks. I find it humiliating and frustrating to be defeated by
> > > software that claims to be useful.
> > >
> > > At any rate, if you can help me with this, I will be eternally grateful - or
> > > at least until I try to write my next macro...
> > >
> > > Mark
> > > Boston, MA
> > > direct: [email protected]
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson