Excel Search and Replace of Formula Referencing Specific Rows

Scripting Forums AppleScript | Mac OS X
,
#1

Hi All,

I have a table of data where column b has names, column c has a value and column d has a formula referencing column c. I want to do a search and replace to modify the formula in column d based on a specific name match in column a.

I created the following script:

set keyColumn to range "B:B"
set searchTerm to "Va"
set writeTerm to "=C7*.10"
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
set rowFound to 0
repeat while rowFound < (get first row index of FoundCell)
	set value of (get offset FoundCell column offset 2) to writeTerm
	set rowFound to first row index of FoundCell
	set FoundCell to find next keyColumn after FoundCell
end repeat

I see where my error is in the third line of this code “ its copying down the formula referencing cell c7 and not changing the row number as it copies the formula down the list. Can anyone help?

#2

R1C1 notation is perfect for this kind of situation
You could change these two lines

set writeTerm to "=RC3/10"

-- ...

set formula R1C1 of (get offset FoundCell column offset 2) to writeTerm

You may have to adjust the Row offset of writeTerm to match what you want.
(What cell do you want to have the formula =C7/10)

#3

Thank you so very much mikerickson. This was perfect!! If you ever make it to Hawaii send me an email - I owe you a drink.