Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto sum macro if data is present above the cell
works like a charm! is there a way to make the totals bold? lik Selection.Font.Bold = True or something? thank you again for the help -- onesain ----------------------------------------------------------------------- onesaint's Profile: http://www.excelforum.com/member.php...nfo&userid=937 View this thread: http://www.excelforum.com/showthread.php?threadid=27095 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto sum macro if data is present above the cell
See added line marked with <<<<<<
Option Explicit Sub AddTotalFormulas() Dim FirstTotalRow As Long Dim FirstValue As Range Dim FormulaTemplate As String Dim SearchRange As Range Dim RowOffset As Long Dim SumFormula As Range Dim WordTotal As Range Const FormulaColumn = 14 FormulaTemplate = "=SUM(R[#]C:R[-1]C)" With ActiveSheet Set SearchRange = Intersect(.UsedRange, .Columns(FormulaColumn - 1)) End With With SearchRange Set WordTotal = .Find(What:="total", _ LookIn:=xlValues, LookAt:=xlPart, _ After:=.Cells(.Cells.Count), _ SearchDirection:=xlNext, _ MatchCase:=False) If WordTotal Is Nothing Then MsgBox "Can't find the word 'total' in column " _ & Chr$(SearchRange.Column + 64) & "!", vbOKOnly Exit Sub Else FirstTotalRow = WordTotal.Row End If End With Do Set SumFormula = WordTotal.Offset(0, 1) With SumFormula Set FirstValue = .Offset(-1, 0) 'correct for 0 or 1 values If FirstValue.Offset(-1, 0) < "" Then Set FirstValue = FirstValue.End(xlUp) End If RowOffset = FirstValue.Row - .Row .FormulaR1C1 = Replace(FormulaTemplate, "#", Format$(RowOffset)) .Font.Bold = True '<<<<<< End With Set WordTotal = SearchRange.FindNext(After:=WordTotal) Loop While WordTotal.Row < FirstTotalRow End Sub On Fri, 22 Oct 2004 15:02:36 -0500, onesaint wrote: works like a charm! is there a way to make the totals bold? like Selection.Font.Bold = True or something? thank you again for the help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto sum macro if data is present above the cell
What if you are summing up a number of columns- Offset with range? Than you Myrna Larson Wrote: See added line marked with <<<<<< Option Explicit Sub AddTotalFormulas() Dim FirstTotalRow As Long Dim FirstValue As Range Dim FormulaTemplate As String Dim SearchRange As Range Dim RowOffset As Long Dim SumFormula As Range Dim WordTotal As Range Const FormulaColumn = 14 FormulaTemplate = "=SUM(R[#]C:R[-1]C)" With ActiveSheet Set SearchRange = Intersect(.UsedRange, .Columns(FormulaColumn - 1)) End With With SearchRange Set WordTotal = .Find(What:="total", _ LookIn:=xlValues, LookAt:=xlPart, _ After:=.Cells(.Cells.Count), _ SearchDirection:=xlNext, _ MatchCase:=False) If WordTotal Is Nothing Then MsgBox "Can't find the word 'total' in column " _ & Chr$(SearchRange.Column + 64) & "!", vbOKOnly Exit Sub Else FirstTotalRow = WordTotal.Row End If End With Do Set SumFormula = WordTotal.Offset(0, 1) With SumFormula Set FirstValue = .Offset(-1, 0) 'correct for 0 or 1 values If FirstValue.Offset(-1, 0) < "" Then Set FirstValue = FirstValue.End(xlUp) End If RowOffset = FirstValue.Row - .Row .FormulaR1C1 = Replace(FormulaTemplate, "#", Format$(RowOffset)) .Font.Bold = True '<<<<<< End With Set WordTotal = SearchRange.FindNext(After:=WordTotal) Loop While WordTotal.Row < FirstTotalRow End Sub On Fri, 22 Oct 2004 15:02:36 -0500, onesaint wrote: works like a charm! is there a way to make the totals bold? like Selection.Font.Bold = True or something? thank you again for the help -- y8g5k3 ----------------------------------------------------------------------- y8g5k3m's Profile: http://www.excelforum.com/member.php...fo&userid=2568 View this thread: http://www.excelforum.com/showthread.php?threadid=27095 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto sum macro if data is present above the cell | Excel Programming | |||
auto sum macro if data is present above the cell | Excel Programming | |||
auto sum macro if data is present above the cell | Excel Programming | |||
auto sum macro if data is present above the cell | Excel Programming | |||
auto sum macro if data is present above the cell | Excel Programming |