Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find String in Cell then Paste Sum in Offset Cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find String in Cell then Paste Sum in Offset Cell
The replies to your earlier post didn't work?
RyanH wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FIND / SEARCH text compare cell to string in 3rd cell | Excel Discussion (Misc queries) | |||
Find word then use offset to change value in another cell | Excel Programming | |||
Paste string on a cell to New Web Query URL | Excel Discussion (Misc queries) | |||
vba help pls - find min based on a condition and return val of an offset cell | Excel Programming | |||
VBA: Add cell value to another worksheet cell using Find and offset | Excel Programming |