I have been searching for awhile now and seem to have hit a brick wall.
I have a spreadsheet that contains data, if it finds the word “gauge” in Column A I would like for it to change the cell in Column B to the word “gauge”
Here is what I have so far -
tell application "Microsoft Excel"
activate
select range "A2"
set FoundResult to false
try
set FoundCell to (find range "f:f" what "gauge")
set FoundResult to true
set j of FoundCell to "gauge"
end try
repeat while FoundResult is true
set FoundResult to false
try
set FoundCell to (find range "f:f" what "gauge")
set FoundResult to true
set j of FoundCell to "gauge"
end try
end repeat
end tell
Let me know what I am doing wrong. It clearly finds the word “gauge” but there is nothing being changed in the file in column b.
Thanks in advance for the info!
Untested, but try.
tell application "Microsoft Excel"
activate
select range "A2"
set FoundResult to false
try
set FoundCell to (find range "f:f" what "gauge")
set Value of (Get Offset FoundCell Column Offset 1) to "Guage"
set FoundResult to true
end try
repeat while FoundResult is true
set FoundResult to false
try
set FoundCell to (find range "f:f" what "gauge")
set Value of (Get Offset FoundCell Column Offset 1) to "Guage"
set FoundResult to true
end try
end repeat
end tell
Thanks for the help Mike!
I tried the script and it will only change the first instance of the word “gauge”.
I wrote the script differently so I can see it loop 10 times and still only changes the first time it sees what we are telling it to find.
Most of the time there will only be one instance but I was trying to cover all of my bases.
Any ideas?
Thanks!
To use Find one should specify the After argument.
tell application "Microsoft Excel"
activate
select range "A2"
set keyColumn to range "F:F"
set searchTerm to "gauge"
set writeTerm to "Gauge"
-- find first occurance
try
set FoundCell to (find keyColumn what searchTerm after (get last cell of keyColumn) search direction search next)
on error
display dialog "No keyword in " & (get address keyColumn) buttons {"OK"} cancel button "OK" default button "OK"
end try
-- loop other occurances
set rowFound to 0
repeat while rowFound < (get first row index of FoundCell)
set value of (get offset FoundCell column offset 1) to writeTerm
set rowFound to first row index of FoundCell
set FoundCell to find next keyColumn after FoundCell
end repeat
end tell
Mike,
You are the man!
Works like a charm and i learned a bit in the process thank you!
Just one more question -
I am trying now to make the program user interactive but I am not able to get Excel to understand if the user puts in the search range. Here is what I have so far.
tell application "Microsoft Excel"
activate
select range "A2"
set keyColumn to display dialog "Enter what you would like to search for" default answer "" buttons {"Cancel", "Ok"} default button "Ok"
set searchTerm to display dialog "Enter what you would like to search for" default answer "" buttons {"Cancel", "Ok"} default button "Ok"
set writeTerm to display dialog "Enter what you would like to change the cell/cells to" default answer "" buttons {"Cancel", "Ok"} default button "Ok"
set keyColumn to range text returned of keyColumn
set searchTerm to text returned of searchTerm
set writeTerm to text returned of writeTerm
-- find first occurance
try
set FoundCell to (find keyColumn what searchTerm after (get last cell of keyColumn) search direction search next)
on error
display dialog "No gauge score found in " & (get address keyColumn) buttons {"OK"} cancel button "OK" default button "OK"
end try
-- loop other occurances
set rowFound to 0
repeat while rowFound < (get first row index of FoundCell)
set value of (get offset FoundCell column offset 1) to writeTerm
set rowFound to first row index of FoundCell
set FoundCell to find next keyColumn after FoundCell
end repeat
end tell
Thanks again!
Thinking about it I would like to ask the user what they would like the offset to be as well. But I am sure this opens a whole other can of worms!
I think this will work
tell application "Microsoft Excel"
activate
-- Excel's input box allows for mouse entry
set keyColumn to get input box prompt "Select a column with the mouse." type range object
if keyColumn = false then
return false -- cancel pressed
end if
-- search the whole column
set defaultSearch to value of (column 1 of row 1 of keyColumn)
set keyColumn to get entire column of (get column 1 of keyColumn)
set keyColumn to range (get address keyColumn) of (worksheet object of keyColumn)
-- get list of possible search terms
set rngUsed to (get used range of (get worksheet object of keyColumn))
set usedKey to intersect range1 rngUsed range2 keyColumn
set searchTermList to {}
repeat with i from 1 to count of cells of usedKey
set oneValue to value of cell i of usedKey
if not (searchTermList contains oneValue) then
copy oneValue to end of searchTermList
end if
end repeat
-- Apple Scripts choose from list of possible search terms
set uiSearchTerm to (choose from list searchTermList with prompt "Select a search term" default items {defaultSearch})
if uiSearchTerm = false then
return false -- cancel pressed
end if
set searchTerm to item 1 of uiSearchTerm
-- Remplacement Value is entered with Excel's input box
-- by typing or clicking on a cell contatining the replacement value
set replacementTerm to input box prompt "Enter the replacement value." type input type as string
if replacementTerm = false then
return false -- cancel pressed
end if
-- user inputs replacement column
set replacementColumn to get input box prompt "Select replacement column." type range object
if replacementColumn = false then
return false -- cancel pressed
end if
set colOffset to (first column index of replacementColumn) - (first column index of keyColumn)
-- find first occurance
try
set FoundCell to (find keyColumn what searchTerm after (get last cell of keyColumn) search direction search next)
on error
display dialog "No " & searchTerm & " found in " & (get address keyColumn) buttons {"OK"} cancel button "OK" default button "OK"
end try
-- loop other occurances
set rowFound to 0
repeat while rowFound < (get first row index of FoundCell)
set value of (get offset FoundCell column offset colOffset) to replacementTerm
set rowFound to first row index of FoundCell
set FoundCell to find next keyColumn after FoundCell
end repeat
end tell