Round a Range in Excel

I am trying to round a range of numbers on an Excel worksheet up. I can do it one a time using a loop but wondered if there was a way to a do it to an entire range. Nearly everything I have tried results in a “into type real” error.

The following works for a single cell but does throw an error -10004 which I cannot catch but gives the correct result.


tell application "Microsoft Excel"
	set Rndx to value of range ("C2:C2")
	try
		set Rndx to round Rndx rounding up
	on error errText
		display dialog "An error:" & errText
	end try
	log Rndx
end tell

This is the result
tell application “Microsoft Excel”
get value of cell “C2:C2”
→ 0.3
round 0.3 rounding up
→ error number -10004
end tell
tell current application
round 0.3 rounding up
→ 1
end tell
tell application “Microsoft Excel”
(1)
end tell

When I change the range to (“C2:C3”) I get the error.

If I loop the range I still seem to have a two step process get the value then round it. Again I have tried a single statement and it gives me the same error.

Thanks

Hi Fredrik

Thanks for getting back to me, I think I over complicated my request. Your solution uses a loop which I was hoping I could avoid. Looping with Excel is time consuming as you know. Managed to solve the two step process and this is now my solution still with a loop and there was not a need to error check.

tell application "Microsoft Excel"
	set USEDROWS to count rows of used range of active sheet
	repeat with Rn from 2 to USEDROWS
		set value of cell ("B" & Rn) to round (value of cell ("B" & Rn) as number) rounding up
		set value of cell ("C" & Rn) to round (value of cell ("C" & Rn) as number) rounding up
	end repeat
end tell

Thanks again

There’s no way to avoid a loop, unfortunately. But you can make it less painful by in a few ways. 1) don’t loop in Excel; 2) grab the entire range value at once and spit it back out all at once after you process it.


tell application "Microsoft Excel"
	
	set USEDROWS to count rows of used range of active sheet
	set RNG to "B2:C" & USEDROWS
	set VALS to string value of range RNG
	
end tell

repeat with ROW from 1 to (count of VALS)
	
	set ROWDATA to (item ROW of VALS)
	repeat with COL from 1 to count of ROWDATA
		set item COL of ROWDATA to round (item COL of ROWDATA) rounding up
	end repeat
	
end repeat

tell application "Microsoft Excel"
	
	set value of range RNG to VALS
	
end tell

The script of roosterboy in the post #4 is 8x faster than yours. This is exactly what other users are hinting to you in this topic. The speed is achieved by placing the repeat loops outside the tell block of the application.

Rooster Boy thank you worked perfectly blindingly fast. I do have a question why are you using the class name ROW? I could shorten the script without it but wanted to check with you

Fredrik thank you I just seem to have an issue running VBA from AppleScript . Also the worksheet I am processing is one downloaded from the Web and is in CSV format so I would first have to convert that which adds an extra step.

It’s not a class name, it’s a variable. That whole section is outside the tell block that targets Excel, so ROW has no special meaning like it would if we were using terms from Excel’s AS dictionary. Since in one of your examples you were addressing a two-column range, then the values you get from Excel will be in the form of a nested list where the outer list is the rows and the inner lists are the columns. You would need two loops to go through these lists and so I named the index variables for looping ROW and COL to make it clear what was going on. I normally wouldn’t name the vars in all caps like that, but I followed your own style from your examples.

Roosterboy

Understand I had 1st removed the repeat so it was in the Excel tell block. It told me it was a class error , should have realized it was part of the Excel suite.

Fredrik

Tried your suggestions but could not get it to work, I always have problems trying to use VBA commands as I mentioned.

Anyway now working and I am grateful for all the help.