Sum Column Y at first blank-bottom cell.

R

ryguy7272

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---
 
D

Dave Peterson

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

Set sumrng = ....


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---
Click to expand...
 
R

ryguy7272

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 said:
Since sumrng is an object (a range variable), you need to use Set:

Set sumrng = ....


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---
Click to expand...
Click to expand...
 
D

Dave Peterson

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).
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---
Click to expand...
 
R

ryguy7272

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 said:
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).
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---
Click to expand...
Click to expand...
 
D

Dave Peterson

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.



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 said:
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).
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---
Click to expand...
Click to expand...
Click to expand...
 
R

ryguy7272

Thank you so much!! It's really amazing what can be done with both VBA and
Excel.

--
RyGuy


Dave Peterson said:
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.



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 said:
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---
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Variable Input 13
export excel data to access table using ADO 0
Open four Wbooks, copy four columns from each to Master Wbook 16
If sh.Name <> Sheet1 Then 3
A simple if <> nn then Msgbox "No Match" 5
Sum a variable length column 1
Code works but goes into endless loop and crashes 1
Search other sheets for numbers & color them 10

Top