I was wondering if I could get some insight into how to accomplish what is essentially a repeat find and replace function in excel? I only started learning applescript last Thursday, so I’m less than a week in (be gentle!) For extra context: I’ve been looking at the A/S Dictionary and it’s been helpful, but it’s sorely lacking in examples of proper syntax, so it leaves a newby like me a bit unsure of how to order my commands.
Specifically, what I want to do is this:
I want the script to look through one specific column in excel (say, for example, column A)
While looking through that one specific column, I want it to find any values belonging to a cell in that column, be they single characters or entire sentences. (so it will go through A1, A2, A3, A4, etc)
When it finds a value in a cell in that one single column, I want it to replace that value with the simple word “correct” (so if it finds a value in A3 it will replace that value with “correct”)
If the script finds cells that do not have any values, I do not want it to put anything in those cells. In other words, leave blank cells blank.
My initial thinking was that it was a simple If/Else statement, so I tested my understanding of if/else statements with this basic script:
tell application "Microsoft Excel"
activate
if value of cell "A1" is "a" then
set value of cell "A1" to "correct"
end if
end tell
That worked, so I was happy, so I thought I could scale up that functionality with some variables and gave this a try:
tell application "Microsoft Excel"
activate
set column_a to range "A:A"
if value of column_a is "" then
set value of column_a to ""
else
set value of column_a to "correct"
end if
end tell
That did NOT work, so I was sad. I tried troubleshooting it, and I figure I might have to break down my code a bit more and add have it repeat, but I haven’t found anything that can quite accomplish what I’m trying to, and while I do have a finite number of values that could occupy column A and could thus code an aggressive series of If statements for every single value and every single cell that was used in the spreadsheet, that’s about 500 lines of code for something that I honestly suspect could be accomplished in 10-20 lines at most.
Anyways, let me know what you guys think and if you have any advice please let me know! I’d really appreciate it!
The fastest way if let excel handle the search for you instead of getting each cell value and match it with AppleScript.
tell application "Microsoft Excel"
tell workbook 1
tell worksheet 1
set theRange to find column 1 what "a" look in values look at whole search order by rows without match case and match byte
end tell
end tell
end tell
What the code above does is:
¢ search for string “a”
¢ search in the first column (column A)
¢ match the value of cell (can also match color, formula etc…)
¢ match the whole cell (= comparison use part for like comparison)
¢ search the order by row and not by column.
¢ make the match case insensitive
¢ make the match a string match and not a byte match
Searching and finding with AppleScript in excel is not automating the search and replace process of Excel but you’re automating the search and replace panel in Excel. It’s quite different how the code works. So to find the next cell, just like in the search and replace panel, you need to invoke the same search again with the find next command.
tell application "Microsoft Excel"
tell workbook 1
tell worksheet 1
set theRange to find next column 1 after theRange
end tell
end tell
end tell
TheRange is defined by the previous command above. You need to repeat the process again and again until you have found and replaced all cells.
find and find next return both a range and we can set the value of a range immediately rather than gathering all cells and update them afterwards.
In other words you need something like this:
tell application "Microsoft Excel"
tell workbook 1
tell worksheet 1
try
set theRange to find column 1 what "a" look in values look at whole search order by rows without match case and match byte
on error
set theRange to missing value
end try
if theRange is not missing value then
set value of theRange to "correct"
set matchedRow to first row index of theRange
end if
repeat until theRange is missing value
try
set theRange to find next column 1 after theRange
on error
set theRange to missing value
end try
if theRange is not missing value then
set value of theRange to "correct"
if first row index of theRange ≤ matchedRow then exit repeat
set matchedRow to first row index of theRange
end if
end repeat
end tell
end tell
end tell
Because we order the search by row and when a match has an higher row value than the previous match we know the search is started from the beginning of the sheet again and we stop continuing searching. This is important if the new cell value contains the substring of the search string when using the ‘look at part’ parameter.
Yes, this works absolutely perfectly! I was able to easily fit it into my existing code (It’s a small part of an applescript that formats an Excel document), and the whole thing runs smoothly.