Hello all:

I have a user defined function that uses range names as an imput.

This function look for some data in the ranges named (you can see this
function below, it is short).

The problem is that when I copy and paste some values in the range the
functions don´t actualize always, nothing change in calculations.

I don´t have this problem if I introduce the values one by one manually, but
it occurs if I use copy and paste.

Sometimes even it doesn´t work pressing F9 and I need to press Alt+Ctrl+F9,
I´d like to be able to use this function as any other one in Excel, without
recalculation problems. Can anybody help.

Thank in advance.

Important note: I usually use the function in one worksheet and the data
ranges are in other worksheet.


Public Function LookInTable(TableName As String, ColVal As String, RowVal As
String) As Variant

Application.Volatile
Dim i As Long, rw As Long, col As Long

With Range(TableName)

For i = 1 To .Columns.Count
If .Cells(1, i).Value = RowVal Then
col = i
Exit For
End If
Next

For i = 1 To .Rows.Count
If .Cells(i, 1).Value = ColVal Then
rw = i
Exit For
End If
Next

End With

LookInTable= Application.Evaluate("=Index(" & TableName & "," & _
rw & "," & col & ")")
End Function