UDF Not Updating As Expected

  • I have a written the function below, but when ever I use it, and for example drag it across lots of cells, they all come up with the same value, and I have to manual click on each one and pres enter to get it to show the right value. I have tried searching but without much luck as I am not sure what I should be searching for. Using application.volatile doesn't help.

  • Re: Udf Not Updating As Expected


    What is the formula meant to do? Using Activecell within a UDF that is used in worksheet cells is asking for trouble and probably unexpected results - you should either use a parameter range value to the function or, if you want to base it on the cell in which the formula resides, then Application.Caller.


  • Re: Udf Not Updating As Expected

    Thanks bryce, but its not that.

    Thank you Parsnip. I just tried using Application.Caller. ... instead of activecell, and it seem to work in the little test sheet i just tried it in. I didnt know about application.caller, my limited vba knowledge didn't know any other way of getting the column number of the cell the formula was in.

    What it was intended to do was to lookup on a row (the row of the cell you give it as a parameter), and hunt back (left) along that row from the column the function is in until it finds a cell with a value in it and print that.

  • Re: Udf Not Updating As Expected

    Ah I see.

    Regarding this:


    didn't know any other way of getting the column number of the cell the formula was in.

    The problem is that the Activecell isn't necessarily the cell that contains the formula - it is whatever cell the user - or perhaps code - has activated. This could be very different from the cell that contains the formula, and indeed could be in another worksheet or even another workbook. This is why this could cause unexpected results.

    As to the UDF, you could actually do this with a straightforward worksheet formula like:


    assuming you have entered this is K1 - then the formula will automatically update the range to finish on the cell to its immediate left when you copy it elsewhere (actually using R1C1 style refs makes this even easier as you just use:


    anywhere on your sheet.

    Hope this helps!


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!