snail excel script

Scripting Forums AppleScript | Mac OS X
,
#1

Hi
Basically what I am trying to do is replace pairs of items in rows, there are 88 pairs, later I will delete rows with more than 2 pairs and less than 1 pair. There are over a thousand rows and over a hundred files. There has to be a faster way than this, though this does work.

repeat
	tell application "Finder"
		activate
		
		open document file "pair var setting.xlsx" of folder "Documents" of folder "bestbuy" of folder "Users" of startup disk
	end tell
	tell application "Microsoft Excel"
		activate
		set screen updating to false
		if get value of range "A1" is "" then exit repeat
		if get value of range "A1" is not "" then
			
			get value of range "A1"
			set g1 to value of range "A1"
			get value of range "B1"
			set r1 to value of range "B1"
			get value of range "C1"
			set g2 to value of range "C1"
			get value of range "D1"
			set r2 to value of range "D1"
			delete range range "A1:D1" shift shift up
			save active workbook
			close active workbook
		end if
	end tell
	tell application "Microsoft Excel"
		activate
		
		set ur to (get used range of active sheet)
		repeat with i from 1 to count row in ur
			set v1 to value of cell 1 of row i
			set v2 to value of cell 2 of row i
			set v3 to value of cell 3 of row i
			set v4 to value of cell 4 of row i
			set v5 to value of cell 5 of row i
			set v6 to value of cell 6 of row i
			set v7 to value of cell 7 of row i
			
			
			
			if v1 is g1 and v2 is g2 then
				set value of cell 1 of row i to r1
				set value of cell 2 of row i to r2
			else if v1 is g1 and v3 is g2 then
				set value of cell 1 of row i to r1
				set value of cell 3 of row i to r2
			else if v1 is g1 and v4 is g2 then
				set value of cell 1 of row i to r1
				set value of cell 4 of row i to r2
			else if v1 is g1 and v5 is g2 then
				set value of cell 1 of row i to r1
				set value of cell 5 of row i to r2
			else if v1 is g1 and v6 is g2 then
				set value of cell 1 of row i to r1
				set value of cell 6 of row i to r2
			else if v1 is g1 and v7 is g2 then
				set value of cell 1 of row i to r1
				set value of cell 7 of row i to r2
			else if v2 is g1 and v3 is g2 then
				set value of cell 2 of row i to r1
				set value of cell 3 of row i to r2
			else if v2 is g1 and v4 is g2 then
				set value of cell 2 of row i to r1
				set value of cell 4 of row i to r2
			else if v2 is g1 and v5 is g2 then
				set value of cell 2 of row i to r1
				set value of cell 5 of row i to r2
			else if v2 is g1 and v6 is g2 then
				set value of cell 2 of row i to r1
				set value of cell 6 of row i to r2
			else if v2 is g1 and v7 is g2 then
				set value of cell 2 of row i to r1
				set value of cell 7 of row i to r1
			else if v3 is g1 and v4 is g2 then
				set value of cell 3 of row i to r1
				set value of cell 4 of row i to r2
			else if v3 is g1 and v5 is g2 then
				set value of cell 3 of row i to r1
				set value of cell 5 of row i to r2
			else if v3 is g1 and v6 is g2 then
				set value of cell 3 of row i to r1
				set value of cell 6 of row i to r2
			else if v3 is g1 and v7 is g2 then
				set value of cell 3 of row i to r1
				set value of cell 7 of row i to r1
			else if v4 is g1 and v5 is g2 then
				set value of cell 4 of row i to r1
				set value of cell 5 of row i to r2
			else if v4 is g1 and v6 is g2 then
				set value of cell 4 of row i to r1
				set value of cell 6 of row i to r2
			else if v4 is g1 and v7 is g2 then
				set value of cell 4 of row i to r1
				set value of cell 7 of row i to r1
			else if v5 is g1 and v6 is g2 then
				set value of cell 5 of row i to r1
				set value of cell 6 of row i to r2
			else if v5 is g1 and v7 is g2 then
				set value of cell 5 of row i to r1
				set value of cell 7 of row i to r1
			else if v6 is g1 and v7 is g2 then
				set value of cell 6 of row i to r1
				set value of cell 7 of row i to r1
			else if v2 is g1 and v1 is g2 then
				set value of cell 1 of row i to r2
				set value of cell 2 of row i to r1
			else if v3 is g1 and v1 is g2 then
				set value of cell 1 of row i to r2
				set value of cell 3 of row i to r1
			else if v4 is g1 and v1 is g2 then
				set value of cell 1 of row i to r2
				set value of cell 4 of row i to r1
			else if v5 is g1 and v1 is g2 then
				set value of cell 1 of row i to r2
				set value of cell 5 of row i to r1
			else if v6 is g1 and v1 is g2 then
				set value of cell 1 of row i to r2
				set value of cell 6 of row i to r1
			else if v7 is g1 and v1 is g2 then
				set value of cell 1 of row i to r2
				set value of cell 7 of row i to r1
			else if v3 is g1 and v2 is g2 then
				set value of cell 2 of row i to r2
				set value of cell 3 of row i to r1
			else if v4 is g1 and v2 is g2 then
				set value of cell 2 of row i to r2
				set value of cell 4 of row i to r1
			else if v5 is g1 and v2 is g2 then
				set value of cell 2 of row i to r2
				set value of cell 5 of row i to r1
			else if v6 is g1 and v2 is g2 then
				set value of cell 2 of row i to r2
				set value of cell 6 of row i to r1
			else if v7 is g1 and v2 is g2 then
				set value of cell 2 of row i to r2
				set value of cell 7 of row i to r1
			else if v4 is g1 and v3 is g2 then
				set value of cell 3 of row i to r2
				set value of cell 4 of row i to r1
			else if v5 is g1 and v3 is g2 then
				set value of cell 3 of row i to r2
				set value of cell 5 of row i to r1
			else if v6 is g1 and v3 is g2 then
				set value of cell 3 of row i to r2
				set value of cell 6 of row i to r1
			else if v7 is g1 and v3 is g2 then
				set value of cell 3 of row i to r2
				set value of cell 7 of row i to r1
			else if v5 is g1 and v4 is g2 then
				set value of cell 4 of row i to r2
				set value of cell 5 of row i to r1
			else if v6 is g1 and v4 is g2 then
				set value of cell 4 of row i to r2
				set value of cell 6 of row i to r1
			else if v7 is g1 and v4 is g2 then
				set value of cell 4 of row i to r2
				set value of cell 7 of row i to r1
			else if v6 is g1 and v5 is g2 then
				set value of cell 5 of row i to r2
				set value of cell 6 of row i to r1
			else if v7 is g1 and v5 is g2 then
				set value of cell 5 of row i to r2
				set value of cell 7 of row i to r1
			else if v7 is g1 and v6 is g2 then
				set value of cell 6 of row i to r2
				set value of cell 7 of row i to r1
				
				
			end if
		end repeat
	end tell
end repeat
tell application "Microsoft Excel"
	activate
	set screen updating to true
end tell

any suggestions?
thanks
bills

#2

whoops!!!
wrong script, has typos and reverse search is not needed.
Hers the version that works, still a snail.

tell application "Finder"
	activate
	
	open document file "pair var setting.xlsx" of folder "Documents" of folder "bestbuy" of folder "Users" of startup disk
end tell
tell application "Microsoft Excel"
	activate
	set screen updating to true
	if get value of range "A1" is "" then exit repeat
	if get value of range "A1" is not "" then
		
		get value of range "A1"
		set g1 to value of range "A1"
		get value of range "B1"
		set r1 to value of range "B1"
		get value of range "C1"
		set g2 to value of range "C1"
		get value of range "D1"
		set r2 to value of range "D1"
		delete range range "A1:D1" shift shift up
		save active workbook
		close active workbook
	end if
	tell application "Microsoft Excel"
		activate
		
		set ur to (get used range of active sheet)
		repeat with i from 1 to count row in ur
			set v1 to value of cell 1 of row i
			set v2 to value of cell 2 of row i
			set v3 to value of cell 3 of row i
			set v4 to value of cell 4 of row i
			set v5 to value of cell 5 of row i
			set v6 to value of cell 6 of row i
			set v7 to value of cell 7 of row i
			set v8 to value of cell 8 of row i
			
			
			
			if v1 is g1 and v2 is g2 then
				set value of cell 1 of row i to r1
				set value of cell 2 of row i to r2
			else if v1 is g1 and v3 is g2 then
				set value of cell 1 of row i to r1
				set value of cell 3 of row i to r2
			else if v1 is g1 and v4 is g2 then
				set value of cell 1 of row i to r1
				set value of cell 4 of row i to r2
			else if v1 is g1 and v5 is g2 then
				set value of cell 1 of row i to r1
				set value of cell 5 of row i to r2
			else if v1 is g1 and v6 is g2 then
				set value of cell 1 of row i to r1
				set value of cell 6 of row i to r2
			else if v1 is g1 and v7 is g2 then
				set value of cell 1 of row i to r1
				set value of cell 7 of row i to r2
			else if v1 is g1 and v8 is g2 then
				set value of cell 1 of row i to r1
				set value of cell 8 of row i to r2
			else if v2 is g1 and v3 is g2 then
				set value of cell 2 of row i to r1
				set value of cell 3 of row i to r2
			else if v2 is g1 and v4 is g2 then
				set value of cell 2 of row i to r1
				set value of cell 4 of row i to r2
			else if v2 is g1 and v5 is g2 then
				set value of cell 2 of row i to r1
				set value of cell 5 of row i to r2
			else if v2 is g1 and v6 is g2 then
				set value of cell 2 of row i to r1
				set value of cell 6 of row i to r2
			else if v2 is g1 and v7 is g2 then
				set value of cell 2 of row i to r1
				set value of cell 7 of row i to r2
			else if v2 is g1 and v8 is g2 then
				set value of cell 2 of row i to r1
				set value of cell 8 of row i to r2
			else if v3 is g1 and v4 is g2 then
				set value of cell 3 of row i to r1
				set value of cell 4 of row i to r2
			else if v3 is g1 and v5 is g2 then
				set value of cell 3 of row i to r1
				set value of cell 5 of row i to r2
			else if v3 is g1 and v6 is g2 then
				set value of cell 3 of row i to r1
				set value of cell 6 of row i to r2
			else if v3 is g1 and v7 is g2 then
				set value of cell 3 of row i to r1
				set value of cell 7 of row i to r2
			else if v3 is g1 and v8 is g2 then
				set value of cell 3 of row i to r1
				set value of cell 8 of row i to r2
			else if v4 is g1 and v5 is g2 then
				set value of cell 4 of row i to r1
				set value of cell 5 of row i to r2
			else if v4 is g1 and v6 is g2 then
				set value of cell 4 of row i to r1
				set value of cell 6 of row i to r2
			else if v4 is g1 and v7 is g2 then
				set value of cell 4 of row i to r1
				set value of cell 7 of row i to r2
			else if v4 is g1 and v8 is g2 then
				set value of cell 4 of row i to r1
				set value of cell 8 of row i to r2
			else if v5 is g1 and v6 is g2 then
				set value of cell 5 of row i to r1
				set value of cell 6 of row i to r2
			else if v5 is g1 and v7 is g2 then
				set value of cell 5 of row i to r1
				set value of cell 7 of row i to r2
			else if v5 is g1 and v8 is g2 then
				set value of cell 5 of row i to r1
				set value of cell 8 of row i to r2
			else if v6 is g1 and v7 is g2 then
				set value of cell 6 of row i to r1
				set value of cell 7 of row i to r2
			else if v6 is g1 and v8 is g2 then
				set value of cell 6 of row i to r1
				set value of cell 8 of row i to r2
			else if v7 is g1 and v8 is g2 then
				set value of cell 7 of row i to r1
				set value of cell 8 of row i to r2
			end if
		end repeat
	end tell
end tell

bills

#3

This might be faster, this replaces the long IF.

tell application "Microsoft Excel"
	-- activate
	
	set ur to (get used range of active sheet)
	repeat with i from 1 to count row in ur
	
		--set {v1, v2, v3, v4, v5, v6, v7, v8} to item 1 of (get value of (get resize (row i) column size 8))
		--set vArray to {v1, v2, v3, v4, v5, v6, v7, v8}
		set vArray to item 1 of (get value of (get resize (row i) column size 8))
		
		set IndexOfG1 to 0
		set IndexOfg2 to 0
		
		repeat with i from 1 to count of vArray
			if item i of vArray = g1 then
				set IndexOfG1 to i
			end if
		end repeat
		
		repeat with i from 1 to count of vArray
			if item i of vArray = g2 then
				set IndexOfg2 to i
			end if
		end repeat
		
		if 0 < IndexOfG1 * IndexOfg2 then
			set value of cell IndexOfG1 of row i to r1
			set value of cell IndexOfg2 of row i to r2
		end if
		
	end repeat
end tell
#4

Hi

much faster,
thanks
bills