Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Text the Insert Sum
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Text the Insert Sum
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Text the Insert Sum
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 "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Text the Insert Sum
That line of code may be redundant/overkill. But it should still work.
Joel wrote: 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 "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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Text the Insert Sum
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. -- (please remember to click yes if replies you receive are helpful to you) "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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find & Replace and Find & Insert macro help needed | Excel Programming | |||
text box insert in Excel - text box lines print on second copy | Excel Discussion (Misc queries) | |||
find text then insert new row above | Excel Programming | |||
Need a macro to insert text in a cell that already has text.Excel | Excel Programming | |||
Insert cell/format/text/fontsize and auto insert into header? | Excel Programming |