Excel Find and Report

Scripting Forums AppleScript | Mac OS X
#1

Hi all,
I’m still pretty new to applescript and working on a simple-ish excel project mainly to teach myself some things. I’m stuck trying to manipulate a search result, and was hoping someone could point me in the right direction…

Basically, I have two excel columns, lets say E and A. I want to search for a specific value in the first column A, then report the value in the cell in the same row of the second column B. So if I input $21,200 as a search term and it is found in $E$227, I want to know the value found in $A$227. Here is what I have so far:


tell application "Microsoft Excel"
		activate
		activate object workbook "PA01_PA14.xlsx"
		activate object worksheet 1
		set rangeToSearch to get range "E:E"
		set PA14cell to (find rangeToSearch what theAnswer)
		set PA01 to get address PA14cell with row absolute
		display dialog PA01
	end tell

This returns the address of the search term - e.g., {$E$227}, but I can’t figure out how to then display the value of $A$227.

Any thoughts? Thanks!

#2

When you find a range, you can get the row of that range using “first row index”. Then you just add an “A” to that and get the value of the cell…

tell application "Microsoft Excel"
	set rangeToSearch to get range "E:E"
	set foundRange to (find rangeToSearch what theAnswer)
	set theRow to first row index of foundRange
	set Avalue to value of cell ("A" & theRow as text)
	display dialog Avalue
end tell