are only the header words and total in Column A - the other data in column B?

This assumes such is the case. Test on a copy of your workbook

Sub ReplaceTotal()
Dim s as String, cell as Range, rng as Range
set rng = range(cells(1,1),cells(rows.count,1).end(xlup))
for each cell in rng
if instr(1,cell,"total",vbTextcompare) then
cell.Value = s
else
s = cell.value
end if
next
end sub

If that isn't the case, how can we determine the header words - indented by
two spaces? No spaces? what?

--
Regards,
Tom Ogilvy



"Ricky Pang" wrote:

Hello Experts,
How do you replace each "Total" with the text that is located
immediately below one line below an empty space? And how do you add a
Message Box notification to either accept or override for each Replace
action?

[space]
Payroll
Contract Labour
Employee Benefits
[space]
Total
[space]
Utilities
Utilities - Electric
Utilities - Gas
[space]
Total

The result:
Msgbox "Total" will be replaced by "Payroll". Option to "OK" to accept,
"Cancel" to not do any replacing, "Override" to manually enter a new
text.

"Total" would become "Payroll"
"Total" would become "Utilities"


I've tried using the Replace function but I needed only code would work
as each Replace is different.

Thanks in advance,
Ricky

*** Sent via Developersdex http://www.developersdex.com ***