UDF Not Updating As Expected

nb- · Jul 31st 2007

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.

Code

Option Explicit


Public Function FirstLinePickUp(inputrow As Variant) As Variant


Dim n As Integer
Dim testcell As Variant


n = 0
testcell = ""


Do Until testcell <> "" Or ActiveCell.Column - n <= 0
testcell = Cells(inputrow.row, ActiveCell.Column - n)
n = n + 1
Loop


FirstLinePickUp = testcell


n = 0
testcell = ""


End Function

Display More

bryce · Jul 31st 2007

Re: Udf Not Updating As Expected

Is your automatic calculation turned off? Try pressing F9 and see if that recalculates the formulas.

RichardSchollar · Jul 31st 2007

Re: Udf Not Updating As Expected

Hi

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.

Richard

nb- · Jul 31st 2007

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.

RichardSchollar · Jul 31st 2007

Re: Udf Not Updating As Expected

Ah I see.

Regarding this:

Quote

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:

=LOOKUP(2,1/($A1:J1<>""),$A1:J1)

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:

=LOOKUP(2,1/(RC1:RC[-1]<>""),RC1:RC[-1])

anywhere on your sheet.

Hope this helps!

Richard

UDF Not Updating As Expected

Participate now!

Share