Find Text the Insert Sum
 

I have a order sheet that has a list of products and there prices. The
prices are located in Column F. I want to beable to Sum all the products in
a Cell that is next to a cell the words Sub Total in it. So I wrote my code
to search Col. E for "Sub Total" and return the address. But I am getting an
error on my variable LastRow, why? Is there an easier way of doing the
subtotal?

For example, if "Sub Total" is in Cells(20,5) then the pricing sum should be
in Cells(20,6). Note: Below the Sub Total Cell, I have Shipping Costs,
Freight, and then TOTAL.

Sub SubTotal()

Dim LastRow As Long
Dim mySubTotal As Range

Set mySubTotal = Sheets("QUOTE").Columns("E:E").Find(What:="Sub Total", _
After:=Cells(6, 5), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

LastRow = Sheets("QUOTE").Range(mySubTotal.Address).Offset(-1, 0).Row
<==Error

Sheets("QUOTE").Range(mySubTotal.Address).Offset(0 , 1).Value =
WorksheetFunction.Sum("F6:F" & LastRow - 1)

End Sub

Find Text the Insert Sum
 

Check your previous post.

RyanH wrote:

I have a order sheet that has a list of products and there prices. The
prices are located in Column F. I want to beable to Sum all the products in
a Cell that is next to a cell the words Sub Total in it. So I wrote my code
to search Col. E for "Sub Total" and return the address. But I am getting an
error on my variable LastRow, why? Is there an easier way of doing the
subtotal?

For example, if "Sub Total" is in Cells(20,5) then the pricing sum should be
in Cells(20,6). Note: Below the Sub Total Cell, I have Shipping Costs,
Freight, and then TOTAL.

Sub SubTotal()

Dim LastRow As Long
Dim mySubTotal As Range

Set mySubTotal = Sheets("QUOTE").Columns("E:E").Find(What:="Sub Total", _
After:=Cells(6, 5), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

LastRow = Sheets("QUOTE").Range(mySubTotal.Address).Offset(-1, 0).Row
<==Error

Sheets("QUOTE").Range(mySubTotal.Address).Offset(0 , 1).Value =
WorksheetFunction.Sum("F6:F" & LastRow - 1)

End Sub

--

Dave Peterson

Find Text the Insert Sum
 

You have too much info in the find statement. the set already defines the
workshhet and range. You don't need to redefine the worksheet and range.

from
LastRow = Sheets("QUOTE").Range(mySubTotal.Address).Offset(-1, 0).Row
to
LastRow = mySubTotal.Offset(-1, 0).Row


"RyanH" wrote:

I have a order sheet that has a list of products and there prices. The
prices are located in Column F. I want to beable to Sum all the products in
a Cell that is next to a cell the words Sub Total in it. So I wrote my code
to search Col. E for "Sub Total" and return the address. But I am getting an
error on my variable LastRow, why? Is there an easier way of doing the
subtotal?

For example, if "Sub Total" is in Cells(20,5) then the pricing sum should be
in Cells(20,6). Note: Below the Sub Total Cell, I have Shipping Costs,
Freight, and then TOTAL.

Sub SubTotal()

Dim LastRow As Long
Dim mySubTotal As Range

Set mySubTotal = Sheets("QUOTE").Columns("E:E").Find(What:="Sub Total", _
After:=Cells(6, 5), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

LastRow = Sheets("QUOTE").Range(mySubTotal.Address).Offset(-1, 0).Row
<==Error

Sheets("QUOTE").Range(mySubTotal.Address).Offset(0 , 1).Value =
WorksheetFunction.Sum("F6:F" & LastRow - 1)

End Sub

Find Text the Insert Sum
 

That line of code may be redundant/overkill. But it should still work.

Joel wrote:

You have too much info in the find statement. the set already defines the
workshhet and range. You don't need to redefine the worksheet and range.

from
LastRow = Sheets("QUOTE").Range(mySubTotal.Address).Offset(-1, 0).Row
to
LastRow = mySubTotal.Offset(-1, 0).Row

"RyanH" wrote:

I have a order sheet that has a list of products and there prices. The
prices are located in Column F. I want to beable to Sum all the products in
a Cell that is next to a cell the words Sub Total in it. So I wrote my code
to search Col. E for "Sub Total" and return the address. But I am getting an
error on my variable LastRow, why? Is there an easier way of doing the
subtotal?

For example, if "Sub Total" is in Cells(20,5) then the pricing sum should be
in Cells(20,6). Note: Below the Sub Total Cell, I have Shipping Costs,
Freight, and then TOTAL.

Sub SubTotal()

Dim LastRow As Long
Dim mySubTotal As Range

Set mySubTotal = Sheets("QUOTE").Columns("E:E").Find(What:="Sub Total", _
After:=Cells(6, 5), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

LastRow = Sheets("QUOTE").Range(mySubTotal.Address).Offset(-1, 0).Row
<==Error

Sheets("QUOTE").Range(mySubTotal.Address).Offset(0 , 1).Value =
WorksheetFunction.Sum("F6:F" & LastRow - 1)

End Sub


--

Dave Peterson

Find Text the Insert Sum
 

Hi Ryan,

Firstly, I have used your code and that line works OK, although the line
after does not.

OK, so you want the row number of the line above the "Sub Total" line. So,
after you have performed the search simply set the LastRow variable thus:

LastRow = mySubTotal.Row -1

When you are entering the formula you are using LastRow -1 again, which
means the total in your formula will now be summing to the row 2 above the
"Sub Total" row. In other words your code was taking 1 off the row value
twice. I assumed this was not what you wanted, so I removed it from the final
row. If you do want to take another 1 off then I'd do it all in the line I
wrote above. To enter the formula use:

Sheets("QUOTE").Range(mySubTotal.Address).Offset(0 , 1).Value = "=SUM(F6:F" &
LastRow - 1 & ")"

You have tried using WorksheetFunction. These are not for entering in cells,
but so that you can use functions that are available in a worksheet within
VBA. What you are trying to achieve is to enter a worksheet function into a
worksheet, so I amended the formula to that above.

One further point, if you are able to then try getting used to using
Subtotal instead of Sum. The reason I suggest this is because you can use
Subtotal many times in a column, and a Subtotal function will not add other
Subtotals in the range. In other words it will not duplicate totalled values.
Using Sum will. I have seen spreadsheets where people have used Sum
throughout, and after each Sum they have a hidden row with a -Sum so that
their final Sum will be correct. Doesn't always affect people, but in general
Subtotal is worth getting used to.

I hope this helps,

Sean.

--
(please remember to click yes if replies you receive are helpful to you)


"RyanH" wrote:

I have a order sheet that has a list of products and there prices. The
prices are located in Column F. I want to beable to Sum all the products in
a Cell that is next to a cell the words Sub Total in it. So I wrote my code
to search Col. E for "Sub Total" and return the address. But I am getting an
error on my variable LastRow, why? Is there an easier way of doing the
subtotal?

For example, if "Sub Total" is in Cells(20,5) then the pricing sum should be
in Cells(20,6). Note: Below the Sub Total Cell, I have Shipping Costs,
Freight, and then TOTAL.

Sub SubTotal()

Dim LastRow As Long
Dim mySubTotal As Range

Set mySubTotal = Sheets("QUOTE").Columns("E:E").Find(What:="Sub Total", _
After:=Cells(6, 5), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

LastRow = Sheets("QUOTE").Range(mySubTotal.Address).Offset(-1, 0).Row
<==Error

Sheets("QUOTE").Range(mySubTotal.Address).Offset(0 , 1).Value =
WorksheetFunction.Sum("F6:F" & LastRow - 1)

End Sub

Find Text the Insert Sum
 

Try checking your previous thread. There was some error checking in that
response.

RyanH wrote:

Thanks for all the help so far. I have revised my code and I am still
getting the error "Runtime Error 91, object variable or With block variable
not set."

Sub SubTotal()

Dim LastRow As Long
Dim mySubTotal As Range

Set mySubTotal = Sheets("QUOTE").Columns("E:E").Find(What:="Sub Total", _
After:=Cells(6, 5), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)


LastRow = mySubTotal.Row <===ERROR ERROR

mySubTotal.Offset(0, 1).Value = "=SUM(F6:F" & LastRow - 1 & ")"

End Sub

Thanks, Ryan

"Dave Peterson" wrote:

Check your previous post.

RyanH wrote:

I have a order sheet that has a list of products and there prices. The
prices are located in Column F. I want to beable to Sum all the products in
a Cell that is next to a cell the words Sub Total in it. So I wrote my code
to search Col. E for "Sub Total" and return the address. But I am getting an
error on my variable LastRow, why? Is there an easier way of doing the
subtotal?

For example, if "Sub Total" is in Cells(20,5) then the pricing sum should be
in Cells(20,6). Note: Below the Sub Total Cell, I have Shipping Costs,
Freight, and then TOTAL.

Sub SubTotal()

Dim LastRow As Long
Dim mySubTotal As Range

Set mySubTotal = Sheets("QUOTE").Columns("E:E").Find(What:="Sub Total", _
After:=Cells(6, 5), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

LastRow = Sheets("QUOTE").Range(mySubTotal.Address).Offset(-1, 0).Row
<==Error

Sheets("QUOTE").Range(mySubTotal.Address).Offset(0 , 1).Value =
WorksheetFunction.Sum("F6:F" & LastRow - 1)

End Sub

--

Dave Peterson


--

Dave Peterson