Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Word Total and Grand Total
I have a spreadsheet where I have used the subtotal
function to get subtotals and grandtotals based on a particular code. However, it would be nice if I could change them to something that makes more sense. The subtotals are in Column E and the subtotals have codes like 32000 Total, 33000 Total. In other words each subtotal has three zeroes and the word Total in common. Col D has names and I would like the subtotal to say offset (-1,-1).Value & "Total". That would give me Jim Total or the Fred Total etc. Where the word Grand Total comes in at the bottom of the range, I would like to change it to Points Total. I would appreciate any help. Thank you. --Indu |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Word Total and Grand Total
You can adapt the sample code for the Find Method
Dim c as Range, firstAddress as String With Worksheets(1).Columns(5) Set c = .Find("Total", lookin:=xlValues, MatchCase:=False) If Not c Is Nothing Then firstAddress = c.Address Do if instr(1,c,"Grand",vbTextcompare) then c.Value = "Point Total" else c.Value = c.Offset(-1,-1).Value & " Total" end if Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With -- Regards Tom Ogilvy "Indu Aronson" wrote in message ... I have a spreadsheet where I have used the subtotal function to get subtotals and grandtotals based on a particular code. However, it would be nice if I could change them to something that makes more sense. The subtotals are in Column E and the subtotals have codes like 32000 Total, 33000 Total. In other words each subtotal has three zeroes and the word Total in common. Col D has names and I would like the subtotal to say offset (-1,-1).Value & "Total". That would give me Jim Total or the Fred Total etc. Where the word Grand Total comes in at the bottom of the range, I would like to change it to Points Total. I would appreciate any help. Thank you. --Indu |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Word Total and Grand Total
Try this:
Dim DataRng as Range Set DataRng = Range("E1:E65535") Dim Cell as Range For each Cell in DataRng 'The 1 makes sure that Excel starts from the first cell 'Because everything is being lowercased, make sure total and grandtotal are always lower cased in the instr statement If instr(1, lcase(cell.value), "000 total") 0 then cell.value = cell.offset(-1, -1).value & " Total" ElseIf InStr(1, LCase(Cell.Value), "grand total") 0 Then Cell.Value = "Points Total" end if Next -----Original Message----- I have a spreadsheet where I have used the subtotal function to get subtotals and grandtotals based on a particular code. However, it would be nice if I could change them to something that makes more sense. The subtotals are in Column E and the subtotals have codes like 32000 Total, 33000 Total. In other words each subtotal has three zeroes and the word Total in common. Col D has names and I would like the subtotal to say offset (-1,-1).Value & "Total". That would give me Jim Total or the Fred Total etc. Where the word Grand Total comes in at the bottom of the range, I would like to change it to Points Total. I would appreciate any help. Thank you. --Indu . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate Running Total Grand Total In Pivot Table | Excel Discussion (Misc queries) | |||
Excel 2002 : Any single button to get sub total and grand total ? | Excel Discussion (Misc queries) | |||
Should grand total equal average of sub total | Excel Discussion (Misc queries) | |||
Pivots - Auto calc % Sub total is of grand total | Excel Discussion (Misc queries) | |||
Adding Data Using Multiple Worksheets to Total into a Grand Total | Excel Worksheet Functions |