Posts by artsjeroen

    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?

    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.

    Re: Variable decimal places using VBA


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


    I've added an example file.
    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).

    Files

    • Example.xlsx

      (10.2 kB, downloaded 83 times, last: )

    Re: Variable decimal places using VBA


    Thanks for the Reply!
    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.

    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?