CODEWITHSUNDEEP
excelvba
Chris N
Chris N

Reputation: 1

Getting "Invalid procedure call or statement" when using VBA Round function in excel

I'm setting up a pricing tool for my sales team and my boss wants values to be rounded to the nearest thousand (i.e. 1000) once I convert the values from USD to another currency. I'm using the first line of code below to set the format of the cell to GBP (british pounds) and then the second line of code to multiply the current amount in that cell (i.e. the .value) against my currency conversion which is in cell 4,11.

Current code that works, but gives a non-rounded result (£4,235):

Cells(arr(rowcnt), ChkCol5).NumberFormat = "[$£-en-GB]#,##0"

Cells(arr(rowcnt), ChkCol5).Value = Cells(arr(rowcnt), ChkCol5).Value * Cells(4, 11)

When I add the "round" function to line 2 though, I get a

"Run time error 5: Invalid procedure call or statement."

Cells(arr(rowcnt), ChkCol5).Value = **Round**(Cells(arr(rowcnt), ChkCol5).Value * Cells(4, 11)**, -3)**

When I use 3 instead of -3 it doesn't error, but also doesn't appear to do anything. Granted, my values are all whole numbers so that may be why. But it's odd to me that I don't get an error rounding with a 3, but I do with a -3.

Any thoughts??

Thanks!

Upvotes: 0

Views: 262

Answers (2)

Chris N
Chris N

Reputation: 1

I updated my line of code to use the worksheet.round function and it now works. Thanks to both BigBen and Ron Rosenfeld for the help.

Cells(arr(rowcnt), ChkCol5).Value = WorksheetFunction.Round(Cells(arr(rowcnt), ChkCol5).Value * Cells(4, 11), -3)

Upvotes: 0

BigBen
BigBen

Reputation: 49998

You need to use WorksheetFunction.Round here.

Notes from the Round function documentation as to why:

numdecimalplaces: Number indicating how many places to the right of the decimal are included in the rounding. If omitted, integers are returned by the Round function.

So a negative number is not a valid second argument.

Also noteworthy:

This VBA function returns something commonly referred to as bankers rounding. So be careful before using this function. For more predictable results, use Worksheet Round functions in Excel VBA.

Upvotes: 1

Related Questions