Excel search variation

Scripting Forums AppleScript | Mac OS X
,
#1

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

#2

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,

#3

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

#4

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.