Excel search variation

Google didn’t turn up anything that described what I was looking for. Here’s a visual example of what I’m trying to do. Column H will look like this:

smoking dinner brunch
smoking dinner
smoking breakfast brunch

I would like to search on column H, clear the entire contents of the cells that contain the word “brunch” and replace it with the letter B.

What I have now is:


repeat
set target to (find (range "H:H") what "Brunch" with match case)
clear contents of target
insert into (character 0 of target) string "B"
end repeat

That works fine, but when it’s found and replaced the last “B”, I get an AppleScript error and I’d really like to learn to do this properly.

Thanks,

Kim

Hi,

I’m using Excel X, but doesn’t your version have the ‘Replace’ command.

tell application “Microsoft Excel”
set the_target to Replace (Range “H:H”) What “Brunch” ReplaceWith “B” with MatchCase
end tell

gl,

Thanks for your reply, kel. What you gave me was a simple find and replace, which is great. I love it. But if I use your code, this:

smoking dinner brunch
smoking dinner
smoking breakfast brunch

will give me a result of this:

smoking dinner B
smoking dinner
smoking breakfast B

and I need it to look like this:

B
smoking dinner
B

Thanks again

Kim:

The issue is your repeat. The error is generated because when it runs out of [b], it cannot perform the [set]. So, use a try block with a repeat exit:


repeat
try
set target to (find (range "H:H") what "Brunch" with match case)
clear contents of target
insert into (character 0 of target) string "B"
on error
exit repeat
end try
end repeat

I could not test this, as I have nothing in Excel to use, so let me know how this works for you.