Find Text the Insert Sum

[RyanH]

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]

Check your previous post.

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

 

[Joel]

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

 

[Dave Peterson]

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

 

[SeanC UK]

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.

 

[RyanH]

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

 

[SeanC UK]

Hi Ryan,

I have used the same code as you in both Excel 2003 and 2007 and it works
fine in both. The only time I have found that it doesn't work is when the
Find method does not find anything. Check that the search term is spelt
correctly. If it is then try right clicking on mySubToatl and selecting Add
Watch. Step through the routine using F8 .When you watch the variable you
should notice a change after the Find line has completed; you will see all
the properties belonging to the cell that is found. If this happens the you
should be able to use the immediate window to type ?mySubtotal.Row (press
enter) to see the row number.

The error you mention should only occur on that line if the Find line does
not work, the line itself is fine.

If you still are unable to get this working then please give any further
information you can and I'm sure someone here will get you up and running.

Cheers,

Sean.

 

[Dave Peterson]

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