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.Inser t
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: