Problem with UDF

[jkr]

I have this UDF:

Function FLOPSLAG(ops As Variant, num As Single, rn As Range, ofs As
Byte)
Dim Taeller As Long
Dim i As Long
i = 0
For Each c In rn.Columns(1).Cells
If c.Value = ops Then
i = i + 1
End If
Next c
If num - CInt(num) <> 0 Or num < 1 Then
FLOPSLAG = CVErr(xlErrNum)
Exit Function
End If
If i < num Then
FLOPSLAG = CVErr(xlErrNA)
Exit Function
End If
Taeller = 0
For Each c In rn.Columns(1).Cells
If c.Value = ops.Value Then
Taeller = Taeller + 1
If Taeller = num Then
FLOPSLAG = c.Offset(0, ofs - 1).Value
Exit Function
End If
End If
Next c
End Function

Is there any way to build i kind of "target" function, so the UDF is
only updated if changes appears i column J in the spreadsheet.I
cannnot change it to a macro, as it has to be a part of a formula.

Jan

 

[Joel]

Use the following

Col = Application.Caller.Column

 

[Jan Kronsell]

The problem is, that this returns the columns the UDF id places in, or have
I misunderstood something?

In column A in one sheet I have this formula:
=flopslag(I2;COUNTIF($I$2:I2;I2);Opslag;2), where FLOPSLAG is my UDF.

Opslag is a named array in another sheet.

This formula is copied to about 60.000 rows in the sheet. WheneverIi type
something anywhere in the sheet the all the formulas are recalculated, which
takes about 30 minutes. The prolem is, that the output of the formulas only
change, if I type in column A, so I want to avoid recalculation if I change
anywhere else than column J.

Jan

 

[Joel]

Then use a worksheet changge function rather than a formula. the problem is
when a formula is a function of another cell you can't tell which cell change
to trigger the worksheet re-calculation using the UDF. The worksheet change
will know which cell actually caused the worksheet to be caculated.

 

[Jan Kronsell]

Maybe thats is the onkly solution.

Jan

Then use a worksheet changge function rather than a formula. the
problem is when a formula is a function of another cell you can't
tell which cell change to trigger the worksheet re-calculation using
the UDF. The worksheet change will know which cell actually caused
the worksheet to be caculated.