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 ***

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 ***

Hi Tom,
Good to hear from you. The header words are also located in column A
and the data are in columns B and beyond. The header word is identified
by jumping up 2 empty spaces from "Total" and picking the first word
below. For example;

[space] --- 1st empty cell
Payroll ----- This is the replacement header word.
Contract Labour
Employee Benefits
[space] --- 2nd empty cell
Total ---- This is the original word and should be replaced with
Payroll.

When I've ran your code, the Total has been replaced with the contents
located within 2nd empty cell. How do you get Total to be replaced with
Payroll?

Thanks so much for your help.

Ricky

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

Tom,
Just to clarify, the header and all titles (subtitles) are all within
column A. The data figures are in column B and beyond.

Thanks,
Ricky



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

Sub ReplaceTotal()
Dim s as String, cell as Range, rng as Range
Dim i as Long
set rng = range(cells(1,1),cells(rows.count,1).end(xlup))
for each cell in rng
if instr(1,cell,"total",vbTextcompare) then
i = cell.row - 2
do until len(Trim(cells(i,1))) = 0
i = i - 1
loop
cell.Value = cells(i +1,1).Value
end if
next
end sub

--
Regards,
Tom Ogilvy


"Ricky Pang" wrote in message
...
Tom,
Just to clarify, the header and all titles (subtitles) are all within
column A. The data figures are in column B and beyond.

Thanks,
Ricky



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

That's amazing Tom. It works and it's just what I am looking for.
Thanks very much.

Is it possible to add a message popup box to indicate the what the
replacement word will be as the cursor jumps through each "Total" so I
could see how far along the page the code has progressed?

Popup Msgbox box with each "Total" found:
"Total" will be replaced by "Payroll".
Option "OK" to accept; or
Option "Cancel" to do no replacements at all; or
Option "Override" to enter a new word.

This would be useful when this spreadsheet is passed on to someone new
in the future.

Much much appreciated.

Ricky

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