Find Text the Apply a SubTotal Next to Text Address
 

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 Apply a SubTotal Next to Text Address
 

How about:

Option Explicit
Sub mySubtotal()

Dim LastRow As Long
Dim LastSubTotal As Range

With Sheets("Quote")
With .Range("e:e")
Set LastSubTotal = .Cells.Find(What:="Sub Total", _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False)
End With

If LastSubTotal Is Nothing Then
MsgBox "Something bad happened?"
'What should happen here
Else
LastRow = LastSubTotal.Row - 1
'did you mean to subtract 1 again???
With LastSubTotal.Offset(0, 1)
.Formula = "=Sum(F6:F" & LastRow - 1 & ")"
'.Value = .Value 'formula or value???
End With
End If
End With
End Sub

It looks for the last occurrence by starting with the topmost cell (.cells(1))
and then looking for the previous occurrence.

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