Find String in Cell then Paste Sum in Offset Cell

[RyanH]

I am new to VBA and having an issue. I have a worksheet that contains
products and there prices (Note: Prices located in Col. F). My users add
products and remove products via UserForm when quoting prices to customers.
This causes the Cell that contains the SubTotal to move up and down as
products are added and removed.

I have tried to write my code to search down Col. E and find the cell
location of the String "Sub Total". Once the String Address is located, I
want to use Offset to show the Sum of the product prices next to the String
Cell in Col. F. I am getting an error "Object variable or block variable not
set" indicated below. Why?

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

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

End Sub

Thanks in Advance,
Ryan

 

[Dave Peterson]

The replies to your earlier post didn't work?

I am new to VBA and having an issue. I have a worksheet that contains
products and there prices (Note: Prices located in Col. F). My users add
products and remove products via UserForm when quoting prices to customers.
This causes the Cell that contains the SubTotal to move up and down as
products are added and removed.

I have tried to write my code to search down Col. E and find the cell
location of the String "Sub Total". Once the String Address is located, I
want to use Offset to show the Sum of the product prices next to the String
Cell in Col. F. I am getting an error "Object variable or block variable not
set" indicated below. Why?

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

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

End Sub

Thanks in Advance,
Ryan