Using some test values, it worked for me:

set rng = Range("A10")
set rng1 = Range("A20")
set rng2 = Range("F1")
p = "Sheet3"
sAddr = "='" & p & "'!R" & rng.Row & "C2:R" & rng1.Row & "C" &
rng2.Column - 1
? sAddr
='Sheet3'!R10C2:R20C5
sName = "MyName"
ActiveWorkbook.names.Add name:=sName, RefersToR1C1:=sAddr
? Range(sName).Address(0,0,xlA1,True)
[Book2]Sheet3!B10:E20

If rng2 is in Column A, you would have a problem.

--
Regards,
Tom Ogilvy



"Gixxer_J_97" wrote in message
...
Hi all,

i am using the following function to
1) Create a named range
2) return the name of that range

i am having a problem with the line:

ActiveWorkbook.names.Add name:=sName, RefersToR1C1:=sAddr

the error is:
Run-time error '1004':
The name is not valid

Public Function GetPageRange(m As String, p As String, c As Integer,
Optional t As String) As String
With Sheets(p)
Dim rng As Range, rng1 As Range, rng2 As Range, sAddr As String,
sName As String
Set rng = Columns(c).Find(m)
sAddr = rng.Address
If Not rng Is Nothing Then
Do
Set rng1 = rng
Set rng = Columns(c).FindNext(rng)
Loop While rng.Address < sAddr
End If
Set rng2 = Range("B3")
Do
Set rng2 = rng2.Offset(0, 1)
Loop While rng2.Value < ""
On Error Resume Next
ActiveWorkbook.names(p & "_" & t).Delete
On Error GoTo 0
sName = p & "_" & t
sAddr = "='" & p & "'!R" & rng.Row & "C2:R" & rng1.Row & "C" &
rng2.Column - 1
ActiveWorkbook.names.Add name:=sName, RefersToR1C1:=sAddr
GetPageRange = p & "_" & t
End With
End Function

any thoughts?

tia!

J