Ranges in VBA
 

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

Ranges in VBA
 

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.




--
Gary''s Student - gsnu2007d


"Mnilo" wrote:

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

Ranges in VBA
 

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" escribió en el
mensaje ...
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.




--
Gary''s Student - gsnu2007d


"Mnilo" wrote:

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

Ranges in VBA
 

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().

--
Gary''s Student - gsnu200768


"Mnilo" wrote:

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" escribió en el
mensaje ...
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.




--
Gary''s Student - gsnu2007d


"Mnilo" wrote:

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

Ranges in VBA
 

Ok,

I started up Excel again and it worked very well.

Thanks for your help.
"Gary''s Student" escribió en el
mensaje ...
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().

--
Gary''s Student - gsnu200768


"Mnilo" wrote:

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" escribió en el
mensaje ...
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.




--
Gary''s Student - gsnu2007d


"Mnilo" wrote:

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