udf stopped updating

Not without seeing the code, and some sample data.

Richard thank you for the link to the site, I found my answer there.

Hi Richard,

Is there any chance you could post the updated reference your link? I'm having the same problem that smileyc, and nothing seems to workout.

Indeed Richard,

Or at least not for me in Venezuela.

Richard,

I am developing an application for fill in some worksheets using a chemical process simulator. In order to give our customer the information, we need to pass this simulator data into the datasheets (same info, nicer presentation).. In this development I use some UDF and the user wants to drag these functions, to speed things up. When dragging the formula to other cells, not very value is updated.

Here is an example of the type of function I'm using. In this application, I use a dummy argument, but is only to try to push VBA to update the value.


Function test(indx As Range) As String
Dim acll As Object, pcll As Object
Dim comp(1 To 9) As String
Dim n As Integer

comp(1) = "METANO"
comp(2) = "ETANO"
comp(3) = "N-PROPANO"
comp(4) = "I-PROPANO"
comp(5) = "N-BUTANO"
comp(6) = "I-BUTANO"
comp(7) = "N-PENTANO"
comp(8) = "N-HEXANO"
comp(9) = "N-HEPTANO"

n = -1

Set acll = ActiveCell
Set pcll = ActiveCell.Offset(n, 0)

Do While n < 0
af = acll.Formula
pf = pcll.Formula

If Left(af, 4) = Left(pf, 4) Then
n = n - 1
Set pcll = ActiveCell.Offset(n, 0)
Else
n = Abs(n)
End If
Loop

test = comp(n)

End Function

The idea behind this function is to check if it was already use in a previous cell, and if it has been used, print the following sustance tag. If not, print the first one on the list. If you copy this code in VBA, apply that function into any worksheet and drag it, the result for every cell will be METANO. Then, if you go to the formula bar, put the cursor on the formula and click enter, each cell will change its value accordingly.

Perhaps you've seen this kind of behivior before and you culd help me.

Regards,

Rubén

Hi Richard,

I have already made my own post. I apologize fior the sins. Thanks anyway.

Regards,