Tuesday, May 11, 2021

#1 2021-04-03 12:40:47 pm

MitchBVI
Member
Registered: 2006-02-14
Posts: 262

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.

Applescript:


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

Offline

 

#2 2021-04-03 03:14:31 pm

Fredrik71
Member
Registered: 2019-10-23
Posts: 707

Re: Round a Range in Excel

Good practise is to only ask for properties that are related to the tell block.
In other words Excel could give you the number and outside the tell block you use the round command.

ex.

Applescript:

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

Here we set and other value in C3

Applescript:

tell application "Microsoft Excel"
   set Rndx to value of range ("C2:C3")
end tell

set roundedUpList to {}
try
   repeat with i from 1 to (count Rndx)
       set theValue to round (item i of Rndx) rounding up
       copy theValue to end of roundedUpList
   end repeat
on error errText
   display dialog "An error:" & errText
end try
return {C2:item 1 of roundedUpList, C3:item 2 of roundedUpList}

Or C2:C5

Applescript:

tell application "Microsoft Excel"
   set Rndx to value of range ("C2:C5")
end tell

set roundedUpList to {}
try
   repeat with i from 1 to (count Rndx)
       set theValue to round (item i of Rndx) rounding up
       set the end of roundedUpList to theValue
   end repeat
on error errText
   display dialog "An error:" & errText
end try
return {C2:item 1 of roundedUpList, C3:item 2 of roundedUpList, C4:item 3 of roundedUpList, C5:item 4 of roundedUpList}

Last edited by Fredrik71 (2021-04-03 03:44:01 pm)


if you are the expert, who will you call if its not your imagination.

Offline

 

#3 2021-04-03 05:03:42 pm

MitchBVI
Member
Registered: 2006-02-14
Posts: 262

Re: Round a Range in Excel

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.

Applescript:

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

Offline

 

#4 2021-04-03 06:31:44 pm

roosterboy
Member
Registered: 2010-04-19
Posts: 40

Re: Round a Range in Excel

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.

Applescript:


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

Offline

 

#5 2021-04-04 12:47:41 am

KniazidisR
Member
From:: Greece
Registered: 2019-03-03
Posts: 1797

Re: Round a Range in Excel

MitchBVI wrote:

Applescript:

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



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.

Last edited by KniazidisR (2021-04-04 12:56:54 am)


Model: MacBook Pro
OS X: Catalina 10.15.4
Web Browser: Safari 14.1
Ram: 4 GB

Offline

 

#6 2021-04-04 01:17:57 am

Fredrik71
Member
Registered: 2019-10-23
Posts: 707

Re: Round a Range in Excel

@MitchBVI
You could also use Excel function to do exactly what you want.

=ROUNDUP(B2;C2)

There B2=Number C2=digits

So you do not need AppleScript to do that kind of thing if you are looking for speed or a solution
without repeat loop.

Last edited by Fredrik71 (2021-04-04 01:21:02 am)


if you are the expert, who will you call if its not your imagination.

Offline

 

#7 2021-04-04 11:32:27 am

MitchBVI
Member
Registered: 2006-02-14
Posts: 262

Re: Round a Range in Excel

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.

Offline

 

#8 2021-04-04 12:30:33 pm

Fredrik71
Member
Registered: 2019-10-23
Posts: 707

Re: Round a Range in Excel

MitchBVI wrote:

...an issue running VBA from AppleScript


You could do something like this to automate the functions in Excel

Applescript:

set theNumberList to {"C2","C3","C4","C5"}
set theRoundList to {}
repeat with i from 1 to (count theNumberList)
set theFunc to "=ROUNDUP(" & item i of theNumberList & ";0)"
set the end of theRoundList to theFunc
end repeat

The theRoundList is a list of strings
{"=ROUNDUP(C2;0)","=ROUNDUP(C3;0)","=ROUNDUP(C4;0)","=ROUNDUP(C5;0)"}

But it could maybe be a slow process... but properly faster and type it manually wink

I was little curious how to do it... so I put 10 values in A1:A10 and run the script below.

Applescript:

set theNumberList to {"A1", "A2", "A3", "A4", "A5", "A6", "A7", "A8", "A9", "A10"}
repeat with i from 1 to 1
   set theString to "=ROUNDUP(" & item i of theNumberList & ";0)"
   tell application "Microsoft Excel" to set value of range ("B1:B10") to theString
end repeat

Last edited by Fredrik71 (2021-04-04 01:25:05 pm)


if you are the expert, who will you call if its not your imagination.

Offline

 

#9 2021-04-04 01:19:17 pm

roosterboy
Member
Registered: 2010-04-19
Posts: 40

Re: Round a Range in Excel

MitchBVI wrote:

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



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.

Offline

 

#10 2021-04-04 02:45:30 pm

MitchBVI
Member
Registered: 2006-02-14
Posts: 262

Re: Round a Range in Excel

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.

Offline

 

#11 2021-04-04 04:05:38 pm

Fredrik71
Member
Registered: 2019-10-23
Posts: 707

Re: Round a Range in Excel

@MitchBVI

What I call functions in Microsoft Excel is formula
https://support.microsoft.com/en-us/off … fad1a47b2a

To type a formula in a cell it begins with =formula_name(parameters)
You could get values from a cells to execute a formula or function to update other cell.

VBA is a scripting language same as AppleScript is.

I think you have misunderstood what its


if you are the expert, who will you call if its not your imagination.

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)