AutoSum macros
 

With alot of reading, I have been able to piece together some code to
auto sum a variable length column. My column data is coming from a
filtered column on a separate worksheet. My problem is, the code stops
when the filtered column from the other sheet has no data present.
(Data is filtered off of date entries and sometimes there is no data
for a specific date). How do I stop the code if there is no data to
sum? Here is the macro:

Sub totalcolumn()
'To position the cursor in the "AutoSum" cell
Range("Q2").End(xlDown).Offset(1, 0).Select
'Determine the Row numbers
vRowTop = 2
vRowBottom = ActiveCell.Offset(-1, 0).Row
'Compute the R[ ] variable
vDiff = vRowBottom - vRowTop + 1
'Enter the formulas
Selection.FormulaR1C1 = "=SUM(R[" & -vDiff & "]C:R[-1]C)"
'Move the cursor one cell to the right
Selection.Offset(0, 1).Select
'To enter the =Sum formula in the second column of data
Selection.FormulaR1C1 = "=SUM(R[" & -vDiff & "]C:R[-1]C)"
Columns("Q:R").Select


End Sub


--
Lvenom
------------------------------------------------------------------------
Lvenom's Profile: http://www.excelforum.com/member.php...o&userid=35358
View this thread: http://www.excelforum.com/showthread...hreadid=551298

AutoSum macros
 

Sub totalcolumn()
'To position the cursor in the "AutoSum" cell
Range("Q2").End(xlDown).Offset(1, 0).Select
'Determine the Row numbers
vRowTop = 2
vRowBottom = ActiveCell.Row - 1
If vRowBottom = Rows.Count And Range("Q2").Value = "" Then
'do nothing
Else
'Compute the R[ ] variable
vDiff = vRowBottom - vRowTop + 1
'Enter the formulas
Selection.FormulaR1C1 = "=SUM(R[" & -vDiff & "]C:R[-1]C)"
'Move the cursor one cell to the right
'To enter the =Sum formula in the second column of data
Selection.Offset(0, 1).FormulaR1C1 = "=SUM(R[" & -vDiff &
"]C:R[-1]C)"
Columns("Q:R").Select
End If
End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Lvenom" wrote in
message ...

With alot of reading, I have been able to piece together some code to
auto sum a variable length column. My column data is coming from a
filtered column on a separate worksheet. My problem is, the code stops
when the filtered column from the other sheet has no data present.
(Data is filtered off of date entries and sometimes there is no data
for a specific date). How do I stop the code if there is no data to
sum? Here is the macro:

Sub totalcolumn()
'To position the cursor in the "AutoSum" cell
Range("Q2").End(xlDown).Offset(1, 0).Select
'Determine the Row numbers
vRowTop = 2
vRowBottom = ActiveCell.Offset(-1, 0).Row
'Compute the R[ ] variable
vDiff = vRowBottom - vRowTop + 1
'Enter the formulas
Selection.FormulaR1C1 = "=SUM(R[" & -vDiff & "]C:R[-1]C)"
'Move the cursor one cell to the right
Selection.Offset(0, 1).Select
'To enter the =Sum formula in the second column of data
Selection.FormulaR1C1 = "=SUM(R[" & -vDiff & "]C:R[-1]C)"
Columns("Q:R").Select


End Sub


--
Lvenom
------------------------------------------------------------------------
Lvenom's Profile:
http://www.excelforum.com/member.php...o&userid=35358
View this thread: http://www.excelforum.com/showthread...hreadid=551298

AutoSum macros
 

Thank you for the response.
After a little playing around ( and learning the concept behind If,
Then, Else and End If), I was able to make this work just fine. Again
thank you for the help, people such as yourself provide a great support
and learning service for others that are less proficient (such as
myself).


--
Lvenom
------------------------------------------------------------------------
Lvenom's Profile: http://www.excelforum.com/member.php...o&userid=35358
View this thread: http://www.excelforum.com/showthread...hreadid=551298