Change Word Total and Grand Total

[Indu Aronson]

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

 

[Tom Ogilvy]

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

 

[RK]

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