Excel: count cells that contain a value

Hi all,
I’ve not scripted Excel before and would like to be able to count the number of cells that contain a value then replace that value with another and lastly display a dialog letting the user know the amount of cells that have had a value replaced, here is what I have so far:

on open thefile
	set thefilealias to thefile as alias
	tell application "Finder" to set original_name to the name of thefilealias
	set the_path to (path to desktop as string) & characters 1 thru -5 of original_name & ".txt" as string
	tell application "Microsoft Excel"
		launch
		open thefile
		select column 6
		set POs to count of cells of selection whose content = "PO"
		replace selection what "PO" replacement "PR" with match case
		save active workbook in the_path as text Mac file format
		close active workbook saving no
	end tell
	display dialog POs & "POs have been replaced!"
end open

My problem is that I’m not sure of the syntax for counting cells that contain a value. If I remove the set POs line and display dialog line the script replaces all occurences of PO with PR in column 6 without error. I just need help with counting the number of cells that have had values replaced.
Thanks,
Nik

Hi Nik,
Not sure if this will point you in the right direction.
When I execute this on a test worksheet the count of POs is returned.

set poCount to 0

tell application "Microsoft Excel"
	set startRow to 1
	set endRow to 53
	set code_list to value of range ("A" & startRow & ":A" & endRow)
	
	repeat with i in code_list
		if (i as text) = "PO" then set poCount to poCount + 1
	end repeat
	
end tell

poCount

Regards,

Nick

Hi Nick,
That seems to be working great. I going to try and incorporate it into my script and I’ll post it back when I’m done! One last question, I noticed you set a variable for the endRow, if I dont know before hand how many rows there are in the workbook do you know any way of telling?
Many Thanks for your help,
Nik

Hi Nick,
Here’s my finished script. I’ve devised a Handler for checking the amount of rows to be processed. I’m sure it’s not the best way to do it so if you have any suggestions for a better way of processing then please let me know.
Thanks again,
Nik

global endRow

on open thefile
	set poCount to 0
	set thefilealias to thefile as alias
	tell application "Finder" to set original_name to the name of thefilealias
	set the_path to (path to desktop as string) & characters 1 thru -5 of original_name & ".txt" as string
	tell application "Microsoft Excel"
		launch
		open thefile
		my countRows()
		select column 6
		set startRow to 1
		set code_list to value of range ("F" & startRow & ":F" & endRow)
		repeat with i in code_list
			set x to i as text
			if x = "PO" then set poCount to poCount + 1
		end repeat
		set poCount to poCount as string
		replace selection what "PO" replacement "PR" with match case
		save active workbook in the_path as text Mac file format
		close active workbook saving no
	end tell
	display dialog (endRow as string) & " rows searched" & return & poCount & " POs have been replaced!" with icon 2
end open

on countRows()
	tell application "Microsoft Excel"
		select column 6
		set endRow to 0
		set Fnumber to 1
		set Fvalue to "start"
		repeat until Fvalue = ""
			set Fvalue to value of range ("F" & Fnumber)
			set Fnumber to Fnumber + 1
			set endRow to endRow + 1
		end repeat
	end tell
end countRows

Hi Nik,
I wasn’t too sure how to do this myself so went searching and found this:-

http://bbs.applescript.net/viewtopic.php?pid=42162

I’ve tried a few of the code snippets and they work pretty well.
Assuming you’re working with columns that don’t have any empty cells, apart from the one at the end, they return the reference of the empty cell. If you do have empty cells then building in something that checks the next few cells after the empty one would be an option.

Hope they’re of use.

Regards,

Nick

EDIT: I see you got there just before me. Sorry for the delay, been a little busy.

Hi Nick,
looks like some good options in that post.
Thanks again for your time,
Nik

Hi Nik,
Having looked at both sets of code the main difference I can see is that the code posted on the other thread uses Excel’s ‘find’ command which is explained in detail on pages 355-357 of the Excel 2004 Scripting Reference.
If there are quite a few cells to search this may work quicker.
I’ve encountered this command before when writing some VBA for Excel.

Regards,

Nick

Hi Nick,
just downloaded the Excel 2004 Scripting Reference, looks like it makes a great read :expressionless:
thanks,
Nik

Great cure for insomnia! :wink: