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
|