Find/Replace with Previous Value Excel X

Scripting Forums AppleScript | Mac OS X
,
#1

Sample Data:


AAA     150    9"   Remarks
BBB      "     8"   Remarks
CCC     200    9"   Remarks
DDD     210    9"   Remarks
EEE      "     7"   Remarks

I need to tell Excel X to find every Double Quote that’s in a cell by itself, and then replace the Double Quote with the value immediately above it.

Any tips or suggestions are appreciated.

#2

I know you asked for Excel X but the new AppleScript dictionary in Excel 2004 is based on the VB object model. You should be able to translate this AppleScript that does work for Excel 2004 to VB code for Excel X


tell application "Microsoft Excel"
	set myRange to range "A1:D5"
	set numCells to count of cells of myRange
	repeat with idx from 1 to numCells
		set curCell to cell idx of myRange
		if value of curCell is equal to """ then
			set cellAbove to get offset curCell row offset -1
			set newVal to the value of cellAbove
			set the value of curCell to newVal
		end if
	end repeat
end tell

Enjoy

#3

Hi,

Think I finally got it to work with the LookAt property. This seems like it should work:

tell application “Microsoft Excel”
Activate
tell Workbook 1
tell Worksheet 1
set data_range to UsedRange
set quote_cell to LastCell data_range Direction xlUp
repeat
try
set quote_cell to (Find data_range What “”" after quote_cell LookAt xlWhole)
on error – not found?
beep 1
exit repeat
end try
set prev_cell to (Offset quote_cell RowOffset -1)
set Value of quote_cell to prev_cell
end repeat
end tell
end tell
end tell

It looks similar to jimmer’s script. Wonder why they changed the wording so much. It returned your data as:

AAA 150 9" Remarks
BBB 150 8" Remarks
CCC 200 9" Remarks
DDD 210 9" Remarks
EEE 210 7" Remarks

in excel. btw, your data had some strange tabs, alternating Option Space and Space.

gl,

#4

Thanks for the tips/snippets. Neither worked correctly for me. I was under the gun on this, so I managed to fake it using system events to just do the correct keystrokes. Took some time, but saved me from Carpal Tunnel.

First I did a “find” for a DQ in Excel, searching by row. Then ran this:



tell application "Microsoft Excel"
	Activate
	
	repeat
		tell Application "System Events"
			keystroke "g" using {command down} -- < find next Double Quote
			keystroke (ASCII character 30) -- < arrow up 1 cell
		end tell
		
		tell Application "Microsoft Excel"
			CopyObject Selection -- < copy value in cell
		end tell
		
		tell Application "System Events"
			keystroke (ASCII character 31) -- < arrow down 1 cell
			keystroke (ASCII character 3) -- < press enter key to paste/deselect
		end tell
	end repeat
end tell

I just had to watch it so when it got to the end of the second column, I could stop it.

thx again.

#5

Hi,

I am shocked that my script didn’t work for you. You are using Office X? ui scripting is a last resort and it doesn’t seem like you really need to go to that. There is a way to do this without ui scripting.

Just an opinion.

gl,

#6

Actually, it did work, but I screwed it up somehow. It was late, my eyes were fuzzy, all that. Your script did work perfectly when I tried it with a fresh brain.

Yeah, but the UI thing got me out of the woods. Saved me from having to learn an entire new library for something I won’t use again for probably 2 years. :rolleyes:

That’s just weird - I keyed that copy straight into the BBS as an example. Funny stuff.

thx again -

rg

#7

Hi BiggerFish,

Thanks for the feedback. Otherwise, I may have been working on this an extra week maybe.

Ui scripting may be a quick fix, but in the long run the advantages are minute.

You can explore the dictionary of an app and the more you do this, you gain steps towards doing this in many apps. Who knows? Maybe, in the future, the hard way may be the easy way.

BTW, I’m still not sure if the script will work in all cases of finding lone double quotes. So if you run upon any bugs, keep posting. I’m interested in this stuff also, as I’m pretty green at scripting Excel.

gl,