hi

This function doesn't work but in the immediate windows that works :

Private Function Range_Table(Table As String) As Range
On Error GoTo TableGen
Table = Application.Substitute(Table, " ", "")
Set Range_Table = Names(Table).RefersToRange
Exit Function
TableGen:
Set Range_Table = ThisWorkbook.Names(Table).RefersToRange
Resume Next
End Function

Do you have a answer,

Thanks

You can't change anything in a worksheet directly from a function that is
called from a worksheet. Functions can only replace their call with a value.
I don't think it works (from a function) in Excel97 either.

--
Kind regards,

Niek Otten

"François" wrote in message
...
hi

This function doesn't work but in the immediate windows that works :

Private Function Range_Table(Table As String) As Range
On Error GoTo TableGen
Table = Application.Substitute(Table, " ", "")
Set Range_Table = Names(Table).RefersToRange
Exit Function
TableGen:
Set Range_Table = ThisWorkbook.Names(Table).RefersToRange
Resume Next
End Function

Do you have a answer,

Thanks

I don't have xl2003 to test it on, so xl2003 might react differently to the
use of Resume next used this way.

Try it this way

Private Function Range_Table(Table As String) As Range
Dim rng as Range
Table = Application.Substitute(Table, " ", "")
On Error Resume Next
Set Rrng = Names(Table).RefersToRange
if rng is nothing then
Set rng = ThisWorkbook.Names(Table).RefersToRange
end if
On Error goto 0
Set Range_Table = rng
End Function

That said, I don't see the point of checking without ThisWorkbook and then
Checking with ThisWorkbook - Although you might have a reason.

--
Regards,
Tom Ogilvy



"François" wrote in message
...
hi

This function doesn't work but in the immediate windows that works :

Private Function Range_Table(Table As String) As Range
On Error GoTo TableGen
Table = Application.Substitute(Table, " ", "")
Set Range_Table = Names(Table).RefersToRange
Exit Function
TableGen:
Set Range_Table = ThisWorkbook.Names(Table).RefersToRange
Resume Next
End Function

Do you have a answer,

Thanks

Thanks for your advice but it doesn't work either

"Tom Ogilvy" wrote:

I don't have xl2003 to test it on, so xl2003 might react differently to the
use of Resume next used this way.

Try it this way

Private Function Range_Table(Table As String) As Range
Dim rng as Range
Table = Application.Substitute(Table, " ", "")
On Error Resume Next
Set Rrng = Names(Table).RefersToRange
if rng is nothing then
Set rng = ThisWorkbook.Names(Table).RefersToRange
end if
On Error goto 0
Set Range_Table = rng
End Function

That said, I don't see the point of checking without ThisWorkbook and then
Checking with ThisWorkbook - Although you might have a reason.

--
Regards,
Tom Ogilvy



"François" wrote in message
...
hi

This function doesn't work but in the immediate windows that works :

Private Function Range_Table(Table As String) As Range
On Error GoTo TableGen
Table = Application.Substitute(Table, " ", "")
Set Range_Table = Names(Table).RefersToRange
Exit Function
TableGen:
Set Range_Table = ThisWorkbook.Names(Table).RefersToRange
Resume Next
End Function

Do you have a answer,

Thanks

Tx for your advice

"François" wrote:

hi

This function doesn't work but in the immediate windows that works :

Private Function Range_Table(Table As String) As Range
On Error GoTo TableGen
Table = Application.Substitute(Table, " ", "")
Set Range_Table = Names(Table).RefersToRange
Exit Function
TableGen:
Set Range_Table = ThisWorkbook.Names(Table).RefersToRange
Resume Next
End Function

Do you have a answer,

Thanks