ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Sum Column Y at first blank-bottom cell. (https://www.excelbanter.com/excel-programming/410607-sum-column-y-first-blank-bottom-cell.html)

ryguy7272
Sum Column Y at first blank-bottom cell.
 
The code below fails on this line:
sumRng = ActiveSheet.Range("Y2:Y" & lastYRow)
The balloon in the VBE says sumRng =Nothing
What am I doing wrong?

Sub Math()
Dim lastRow As Long
Dim c As Variant
Dim sh As Worksheet
Dim myA As Range
Dim lastYRow As Long, sumRng As Range, Tot As Double

For Each sh In Worksheets

If (sh.Name) < "Sheet1" Then
sh.Activate

Rows("1:1").Select
Selection.Font.Bold = True

lastRow = Cells(Rows.Count, "F").End(xlUp).Row
For Each c In Range("F2:F" & lastRow)
If c.Value < "" Then
c.Offset(, 19).Value = "=RC[-17]*RC[-2]"
End If
Next c

lastYRow = Cells(Rows.Count, "Y").End(xlUp).Row
sumRng = ActiveSheet.Range("Y2:Y" & lastYRow)
Tot = Application.WorksheetFunction.Sum(sumRng)
MsgBox Tot
ActiveSheet.Rows(lastRow + 1) = Tot

Rows("2:2").Select
Selection.Delete Shift:=xlUp

End If

Next sh

Sheets("Sheet1").Select

End Sub

Regards,
Ryan---

--
RyGuy

Dave Peterson
Sum Column Y at first blank-bottom cell.
 
Since sumrng is an object (a range variable), you need to use Set:

Set sumrng = ....



ryguy7272 wrote:

The code below fails on this line:
sumRng = ActiveSheet.Range("Y2:Y" & lastYRow)
The balloon in the VBE says sumRng =Nothing
What am I doing wrong?

Sub Math()
Dim lastRow As Long
Dim c As Variant
Dim sh As Worksheet
Dim myA As Range
Dim lastYRow As Long, sumRng As Range, Tot As Double

For Each sh In Worksheets

If (sh.Name) < "Sheet1" Then
sh.Activate

Rows("1:1").Select
Selection.Font.Bold = True

lastRow = Cells(Rows.Count, "F").End(xlUp).Row
For Each c In Range("F2:F" & lastRow)
If c.Value < "" Then
c.Offset(, 19).Value = "=RC[-17]*RC[-2]"
End If
Next c

lastYRow = Cells(Rows.Count, "Y").End(xlUp).Row
sumRng = ActiveSheet.Range("Y2:Y" & lastYRow)
Tot = Application.WorksheetFunction.Sum(sumRng)
MsgBox Tot
ActiveSheet.Rows(lastRow + 1) = Tot

Rows("2:2").Select
Selection.Delete Shift:=xlUp

End If

Next sh

Sheets("Sheet1").Select

End Sub

Regards,
Ryan---

--
RyGuy

--

Dave Peterson

ryguy7272
Sum Column Y at first blank-bottom cell.
 
Thanks Dave! The errors stopped, but the sum is not correct. Also, all
columns seem to have a sum at the bottom (first blank at bottom). How can I
sum just the Column Y and have the result appear in the first blank cell?

Regards,
Ryan---

--
RyGuy


"Dave Peterson" wrote:

Since sumrng is an object (a range variable), you need to use Set:

Set sumrng = ....



ryguy7272 wrote:

The code below fails on this line:
sumRng = ActiveSheet.Range("Y2:Y" & lastYRow)
The balloon in the VBE says sumRng =Nothing
What am I doing wrong?

Sub Math()
Dim lastRow As Long
Dim c As Variant
Dim sh As Worksheet
Dim myA As Range
Dim lastYRow As Long, sumRng As Range, Tot As Double

For Each sh In Worksheets

If (sh.Name) < "Sheet1" Then
sh.Activate

Rows("1:1").Select
Selection.Font.Bold = True

lastRow = Cells(Rows.Count, "F").End(xlUp).Row
For Each c In Range("F2:F" & lastRow)
If c.Value < "" Then
c.Offset(, 19).Value = "=RC[-17]*RC[-2]"
End If
Next c

lastYRow = Cells(Rows.Count, "Y").End(xlUp).Row
sumRng = ActiveSheet.Range("Y2:Y" & lastYRow)
Tot = Application.WorksheetFunction.Sum(sumRng)
MsgBox Tot
ActiveSheet.Rows(lastRow + 1) = Tot

Rows("2:2").Select
Selection.Delete Shift:=xlUp

End If

Next sh

Sheets("Sheet1").Select

End Sub

Regards,
Ryan---

--
RyGuy

--

Dave Peterson

Dave Peterson
Sum Column Y at first blank-bottom cell.
 
Option Explicit
Sub Math()
Dim lastRow As Long
Dim c As Variant
Dim sh As Worksheet
Dim myA As Range
Dim lastYRow As Long
Dim sumRng As Range
Dim Tot As Double

For Each sh In Worksheets
If LCase(sh.Name) < LCase("Sheet1") Then
With sh
.Rows(1).Font.Bold = True

lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
For Each c In .Range("F2:F" & lastRow)
If c.Value < "" Then
c.Offset(, 19).Value = "=RC[-17]*RC[-2]"
End If
Next c

lastYRow = .Cells(.Rows.Count, "Y").End(xlUp).Row
Set sumRng = .Range("Y2:Y" & lastYRow)
Tot = Application.WorksheetFunction.Sum(sumRng)
MsgBox Tot
.Cells(lastRow + 1, "Y").Value = Tot

.Rows(2).Delete
End With

End If

Next sh

End Sub

One more thing...

You're deleting row 2 at the end of that loop.

What happens if row 2 contains a number? It'll be added to your total.

I think I'd exclude it from the sum or delete it before you do anything (well,
if you really don't want it included).

ryguy7272 wrote:

The code below fails on this line:
sumRng = ActiveSheet.Range("Y2:Y" & lastYRow)
The balloon in the VBE says sumRng =Nothing
What am I doing wrong?

Sub Math()
Dim lastRow As Long
Dim c As Variant
Dim sh As Worksheet
Dim myA As Range
Dim lastYRow As Long, sumRng As Range, Tot As Double

For Each sh In Worksheets

If (sh.Name) < "Sheet1" Then
sh.Activate

Rows("1:1").Select
Selection.Font.Bold = True

lastRow = Cells(Rows.Count, "F").End(xlUp).Row
For Each c In Range("F2:F" & lastRow)
If c.Value < "" Then
c.Offset(, 19).Value = "=RC[-17]*RC[-2]"
End If
Next c

lastYRow = Cells(Rows.Count, "Y").End(xlUp).Row
sumRng = ActiveSheet.Range("Y2:Y" & lastYRow)
Tot = Application.WorksheetFunction.Sum(sumRng)
MsgBox Tot
ActiveSheet.Rows(lastRow + 1) = Tot

Rows("2:2").Select
Selection.Delete Shift:=xlUp

End If

Next sh

Sheets("Sheet1").Select

End Sub

Regards,
Ryan---

--
RyGuy

--

Dave Peterson

ryguy7272
Sum Column Y at first blank-bottom cell.
 
Thanks Dave. Just out of curosity, how can I get the =sum() function in that
first blank cell? That Application.WorksheetFunction.Sum(sumRng) is pretty
neat, but I was hoping to be able to use the =sum() function because it will
be a lot easier to audit.

Regards,
Ryan--


--
RyGuy


"Dave Peterson" wrote:

Option Explicit
Sub Math()
Dim lastRow As Long
Dim c As Variant
Dim sh As Worksheet
Dim myA As Range
Dim lastYRow As Long
Dim sumRng As Range
Dim Tot As Double

For Each sh In Worksheets
If LCase(sh.Name) < LCase("Sheet1") Then
With sh
.Rows(1).Font.Bold = True

lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
For Each c In .Range("F2:F" & lastRow)
If c.Value < "" Then
c.Offset(, 19).Value = "=RC[-17]*RC[-2]"
End If
Next c

lastYRow = .Cells(.Rows.Count, "Y").End(xlUp).Row
Set sumRng = .Range("Y2:Y" & lastYRow)
Tot = Application.WorksheetFunction.Sum(sumRng)
MsgBox Tot
.Cells(lastRow + 1, "Y").Value = Tot

.Rows(2).Delete
End With

End If

Next sh

End Sub

One more thing...

You're deleting row 2 at the end of that loop.

What happens if row 2 contains a number? It'll be added to your total.

I think I'd exclude it from the sum or delete it before you do anything (well,
if you really don't want it included).

ryguy7272 wrote:

The code below fails on this line:
sumRng = ActiveSheet.Range("Y2:Y" & lastYRow)
The balloon in the VBE says sumRng =Nothing
What am I doing wrong?

Sub Math()
Dim lastRow As Long
Dim c As Variant
Dim sh As Worksheet
Dim myA As Range
Dim lastYRow As Long, sumRng As Range, Tot As Double

For Each sh In Worksheets

If (sh.Name) < "Sheet1" Then
sh.Activate

Rows("1:1").Select
Selection.Font.Bold = True

lastRow = Cells(Rows.Count, "F").End(xlUp).Row
For Each c In Range("F2:F" & lastRow)
If c.Value < "" Then
c.Offset(, 19).Value = "=RC[-17]*RC[-2]"
End If
Next c

lastYRow = Cells(Rows.Count, "Y").End(xlUp).Row
sumRng = ActiveSheet.Range("Y2:Y" & lastYRow)
Tot = Application.WorksheetFunction.Sum(sumRng)
MsgBox Tot
ActiveSheet.Rows(lastRow + 1) = Tot

Rows("2:2").Select
Selection.Delete Shift:=xlUp

End If

Next sh

Sheets("Sheet1").Select

End Sub

Regards,
Ryan---

--
RyGuy

--

Dave Peterson

Dave Peterson
Sum Column Y at first blank-bottom cell.
 
First, there was a mistake in the original code.

This line:
..Cells(lastRow + 1, "Y").Value = Tot
referred to lastrow instead of lastYRow.

I didn't notice it before.

But there's no reason you can't reuse that variable again -- and make sure you
don't make an important typo <bg.



Option Explicit
Sub Math2()
Dim lastRow As Long
Dim c As Variant
Dim sh As Worksheet

For Each sh In Worksheets
If LCase(sh.Name) < LCase("Sheet1") Then
With sh
.Rows(1).Font.Bold = True

lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
For Each c In .Range("F2:F" & lastRow)
If c.Value < "" Then
c.Offset(, 19).Value = "=RC[-17]*RC[-2]"
End If
Next c

lastRow = .Cells(.Rows.Count, "Y").End(xlUp).Row
.Cells(lastRow + 1, "Y").FormulaR1C1 _
= "=sum(r3c:r[-1]c)"

.Rows(2).Delete
End With

End If

Next sh

End Sub

This formular1c1
=sum(r3c:r[-1]c)
means to start at row 3 of the same column through the row above the cell with
the formula r[-1] of the same column.




ryguy7272 wrote:

Thanks Dave. Just out of curosity, how can I get the =sum() function in that
first blank cell? That Application.WorksheetFunction.Sum(sumRng) is pretty
neat, but I was hoping to be able to use the =sum() function because it will
be a lot easier to audit.

Regards,
Ryan--

--
RyGuy

"Dave Peterson" wrote:

Option Explicit
Sub Math()
Dim lastRow As Long
Dim c As Variant
Dim sh As Worksheet
Dim myA As Range
Dim lastYRow As Long
Dim sumRng As Range
Dim Tot As Double

For Each sh In Worksheets
If LCase(sh.Name) < LCase("Sheet1") Then
With sh
.Rows(1).Font.Bold = True

lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
For Each c In .Range("F2:F" & lastRow)
If c.Value < "" Then
c.Offset(, 19).Value = "=RC[-17]*RC[-2]"
End If
Next c

lastYRow = .Cells(.Rows.Count, "Y").End(xlUp).Row
Set sumRng = .Range("Y2:Y" & lastYRow)
Tot = Application.WorksheetFunction.Sum(sumRng)
MsgBox Tot
.Cells(lastRow + 1, "Y").Value = Tot

.Rows(2).Delete
End With

End If

Next sh

End Sub

One more thing...

You're deleting row 2 at the end of that loop.

What happens if row 2 contains a number? It'll be added to your total.

I think I'd exclude it from the sum or delete it before you do anything (well,
if you really don't want it included).

ryguy7272 wrote:

The code below fails on this line:
sumRng = ActiveSheet.Range("Y2:Y" & lastYRow)
The balloon in the VBE says sumRng =Nothing
What am I doing wrong?

Sub Math()
Dim lastRow As Long
Dim c As Variant
Dim sh As Worksheet
Dim myA As Range
Dim lastYRow As Long, sumRng As Range, Tot As Double

For Each sh In Worksheets

If (sh.Name) < "Sheet1" Then
sh.Activate

Rows("1:1").Select
Selection.Font.Bold = True

lastRow = Cells(Rows.Count, "F").End(xlUp).Row
For Each c In Range("F2:F" & lastRow)
If c.Value < "" Then
c.Offset(, 19).Value = "=RC[-17]*RC[-2]"
End If
Next c

lastYRow = Cells(Rows.Count, "Y").End(xlUp).Row
sumRng = ActiveSheet.Range("Y2:Y" & lastYRow)
Tot = Application.WorksheetFunction.Sum(sumRng)
MsgBox Tot
ActiveSheet.Rows(lastRow + 1) = Tot

Rows("2:2").Select
Selection.Delete Shift:=xlUp

End If

Next sh

Sheets("Sheet1").Select

End Sub

Regards,
Ryan---

--
RyGuy

--

Dave Peterson


--

Dave Peterson

ryguy7272
Sum Column Y at first blank-bottom cell.
 
Thank you so much!! It's really amazing what can be done with both VBA and
Excel.

--
RyGuy


"Dave Peterson" wrote:

First, there was a mistake in the original code.

This line:
..Cells(lastRow + 1, "Y").Value = Tot
referred to lastrow instead of lastYRow.

I didn't notice it before.

But there's no reason you can't reuse that variable again -- and make sure you
don't make an important typo <bg.



Option Explicit
Sub Math2()
Dim lastRow As Long
Dim c As Variant
Dim sh As Worksheet

For Each sh In Worksheets
If LCase(sh.Name) < LCase("Sheet1") Then
With sh
.Rows(1).Font.Bold = True

lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
For Each c In .Range("F2:F" & lastRow)
If c.Value < "" Then
c.Offset(, 19).Value = "=RC[-17]*RC[-2]"
End If
Next c

lastRow = .Cells(.Rows.Count, "Y").End(xlUp).Row
.Cells(lastRow + 1, "Y").FormulaR1C1 _
= "=sum(r3c:r[-1]c)"

.Rows(2).Delete
End With

End If

Next sh

End Sub

This formular1c1
=sum(r3c:r[-1]c)
means to start at row 3 of the same column through the row above the cell with
the formula r[-1] of the same column.




ryguy7272 wrote:

Thanks Dave. Just out of curosity, how can I get the =sum() function in that
first blank cell? That Application.WorksheetFunction.Sum(sumRng) is pretty
neat, but I was hoping to be able to use the =sum() function because it will
be a lot easier to audit.

Regards,
Ryan--

--
RyGuy

"Dave Peterson" wrote:

Option Explicit
Sub Math()
Dim lastRow As Long
Dim c As Variant
Dim sh As Worksheet
Dim myA As Range
Dim lastYRow As Long
Dim sumRng As Range
Dim Tot As Double

For Each sh In Worksheets
If LCase(sh.Name) < LCase("Sheet1") Then
With sh
.Rows(1).Font.Bold = True

lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
For Each c In .Range("F2:F" & lastRow)
If c.Value < "" Then
c.Offset(, 19).Value = "=RC[-17]*RC[-2]"
End If
Next c

lastYRow = .Cells(.Rows.Count, "Y").End(xlUp).Row
Set sumRng = .Range("Y2:Y" & lastYRow)
Tot = Application.WorksheetFunction.Sum(sumRng)
MsgBox Tot
.Cells(lastRow + 1, "Y").Value = Tot

.Rows(2).Delete
End With

End If

Next sh

End Sub

One more thing...

You're deleting row 2 at the end of that loop.

What happens if row 2 contains a number? It'll be added to your total.

I think I'd exclude it from the sum or delete it before you do anything (well,
if you really don't want it included).

ryguy7272 wrote:

The code below fails on this line:
sumRng = ActiveSheet.Range("Y2:Y" & lastYRow)
The balloon in the VBE says sumRng =Nothing
What am I doing wrong?

Sub Math()
Dim lastRow As Long
Dim c As Variant
Dim sh As Worksheet
Dim myA As Range
Dim lastYRow As Long, sumRng As Range, Tot As Double

For Each sh In Worksheets

If (sh.Name) < "Sheet1" Then
sh.Activate

Rows("1:1").Select
Selection.Font.Bold = True

lastRow = Cells(Rows.Count, "F").End(xlUp).Row
For Each c In Range("F2:F" & lastRow)
If c.Value < "" Then
c.Offset(, 19).Value = "=RC[-17]*RC[-2]"
End If
Next c

lastYRow = Cells(Rows.Count, "Y").End(xlUp).Row
sumRng = ActiveSheet.Range("Y2:Y" & lastYRow)
Tot = Application.WorksheetFunction.Sum(sumRng)
MsgBox Tot
ActiveSheet.Rows(lastRow + 1) = Tot

Rows("2:2").Select
Selection.Delete Shift:=xlUp

End If

Next sh

Sheets("Sheet1").Select

End Sub

Regards,
Ryan---

--
RyGuy

--

Dave Peterson


--

Dave Peterson


All times are GMT +1. The time now is 06:26 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com