Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find cell address of last cell in a column with text | Excel Worksheet Functions | |||
Search, find or lookup defined text in text string | Excel Worksheet Functions | |||
Find text String and select cell address where it is found? | Excel Programming | |||
to find the text and to know that particular cell address | Excel Programming | |||
open some txt files ,find text , copy the text before that to a single cell | Excel Programming |