Ranges in VBA

[Mnilo]

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

 

[Gary''s Student]

The string thing is tripping you up. Consider two functions:

Function zum(r As Range) As Variant
zum = r.Value
End Function

and the function

Function zun(s As String) As Variant
Set r = Range(s)
zun = r.Value
End Function

In the worksheet we have defined mycell as cell A10. In two other cells we
enter:

=zum(mycell)
=zun("mycell")

Both functions work the first time they are entered. If we start updating
A10 either with edits or paste, zum responds and zun does not. This is true
even though zum has no Volatile statement in it.

Change both your code and the worksheet cells to pass the Named Range as a
range, not a string.

 

[Mnilo]

This solution only works if the ranges are in the same worksheet, but it
doesn´t work in other case

Any other suggestion??

Thanks in any case.

 

[Gary''s Student]

It don't see the failure.

=zum(mycell)

give updated results in which ever sheet the formula is entered. Even
though mycell is a Named Range on Sheet1, the Name is visible on any sheet in
the workbook.

Start with a completely new workbook and try zum().

 

[Mnilo]

Ok,

I started up Excel again and it worked very well.

Thanks for your help.