I'm trying to change cell N that contains the subcategory of a music track if it finds the track title I'm looking for in column R.
(The same track titles appear multiple time across the sheet and I'm not allowed to modify the Excel to do a pivot table or to add a macro.)
-- Declare variables
property searchTrackTitle : ""
property newSubCategory : ""
-- Display input box to get search term
display dialog "Enter the track title that you want to search for:" default answer ""
set searchTrackTitle to text returned of result
-- Display input box to get new SubCategory
display dialog "Enter the new SubCategory that you want to use:" default answer ""
set newSubCategory to text returned of result
-- Open Excel and set variables
tell application "Microsoft Excel"
activate
set sh to active sheet
set rng to range "R:R" of sh
end tell
-- Loop through each cell in the range
repeat with cell in rng
-- Check if the cell value matches the track title
if value of cell is searchTrackTitle then
-- Update the SubCategory for the track title
set (text of cell(("N") as text)) of sh to newSubCategory
end if
end repeat
I think I've got something working without needing a sample workbook.
I would not use AppleScript's display dialogs for user text entry – I'd use Keyboard Maestro's Prompt for User Input action and pipe that into your AppleScript action.
Here's how to do that:
# NOTE – the AppleScript variables are actual variables.
# The Keyboard Maestro variable NAMES are STRINGS.
set kmInstance to system attribute "KMINSTANCE"
tell application "Keyboard Maestro Engine"
# Set a Keyboard Maestro variable:
set asVarName to getvariable "local_copiedText" instance kmInstance
# Get a Keyboard Maestro variable
setvariable "local_KM_VarName" instance kmInstance to dataStr
end tell
Your Microsoft Excel Script:
--------------------------------------------------------
# Auth: Christopher Stone
# dCre: 2022/12/16 04:29
# dMod: 2022/12/16 04:29
# Appl: Microsoft Excel
# Task: Find the Given Text in the Given Column – Insert Text in Cell N of that Row.
# Libs: None
# Osax: None
# Tags: @Applescript, @Script, @Microsoft_Excel, @Find, @Search, @Replace
--------------------------------------------------------
set findText to "ambulance"
set insertText to "Notorious"
tell application "Microsoft Excel"
tell active sheet of active workbook
set foundRange to find range "R:R" what findText
set rowNumber to first row index of foundRange
select foundRange
set value of cell ("N" & rowNumber) to insertText
end tell
end tell
--------------------------------------------------------
--------------------------------------------------------
# Auth: Christopher Stone
# dCre: 2022/12/16 04:29
# dMod: 2022/12/16 12:28
# Appl: Microsoft Excel
# Task: Find the Given Text in the Given Column – Insert Text in Cell N of that Row v2.00.
# : Find-Text may occur multiple times in the given column
# Libs: None
# Osax: None
# Tags: @Applescript, @Script, @Microsoft_Excel, @Find, @Search, @Replace
# Vers: 2.00
--------------------------------------------------------
set findText to "Traveling Light"
set replaceText to "Blues, Soul, Rock"
set onceFlag to true
tell application "Microsoft Excel"
tell active sheet of active workbook
repeat
if onceFlag then
set foundRange to find range "R:R" what findText search order search next
copy foundRange to foundRangeOne
set foundRangeARowIndex to foundRange's first row index
set onceFlag to false
else
set foundRange to find next range "R:R" after foundRange
if foundRangeOne = foundRange then exit repeat
set foundRangeBRowIndex to foundRange's first row index
end if
set rowNumber to first row index of foundRange
# select foundRange
set value of cell ("N" & rowNumber) to replaceText
end repeat
end tell
end tell
--------------------------------------------------------
