# Posts by artsjeroen

• ## Ignore blank cells in array formula

Re: Ignore blank cells in array formula

That did the trick! Thanks a lot!

• ## Ignore blank cells in array formula

I've got a list with productnumbers. Like this:
RS0001
RS0002
RS0030
RS0050

All numbers are RS followed by four numbers.

I use the following array formula to find the highest number:

{="RS"&TEXT(MAX(--(RIGHT(\$B\$1:\$B\$100,LEN(\$B\$1:\$B\$100)-2))),"0000")}

However the list goes further than B100, but I can't use B:B because there are also empty cells in column B which gives #Value!.
How can I get this formula to ignore blanks?

• ## Variable decimal places using VBA

Re: Variable decimal places using VBA

Thanks! that will do the trick!

• ## Variable decimal places using VBA

Re: Variable decimal places using VBA

Wow thank you so much! that does the trick!

• ## Variable decimal places using VBA

Re: Variable decimal places using VBA

Thanks! However that formula doesn't do anything to my numbers :wow:
However, if I format the numbers as "Number", 2.212 ends up in 2.200 if the specification is 2.2.

I guess one of my settings in excel is not right.

• ## Variable decimal places using VBA

Re: Variable decimal places using VBA

=ROUND([existing formula here],LEN(A1)-LEN(LEFT(A1,FIND(".",A1))))

This could work, only the formula skips zero's at the end. (e.g., 2.10 rounds to 1 decimal)

• ## Variable decimal places using VBA

Re: Variable decimal places using VBA

Both options don't work for me, I can't figure out why.

The grey areas are filled in by the user. The result is then calculated with a simple formula. The result should have the same decimal numbers as the number in column A (or C).

• ## Variable decimal places using VBA

Re: Variable decimal places using VBA

For some reason the code messes up decimal separator and the 1000 separator.
The numbers in Column B are calculated with formulas. The results of these formulas should have the same amount of decimals as the numbers given in Column A.

• ## Variable decimal places using VBA

I've got a worksheet with specification for some of my products. And when the products are tested the results should be rounded to the nearest number with the same amount of decimal places as given in the specifications.

Example:
Specifications: 2.03 - 2.48 --> Result is 2.2456 this should be rounded to 2.25
Specifications: 13500 - 16500 --> Result is 15680.1234 this result should be rounded to 15680

The VBA code should look for the amount of decimal places in the specifications and use that value to round the result value. Is there a way to do this?

Any suggestions?