Refer to a named range

D

Dave

Hi
XL2003
I am having trouble using a named range in a macro, even after refering the
Help.
The named range lives in the active workbook on another sheet.
My code is this:

Private Sub Worksheet_Activate()
With [CompNames]
Set c = .Find(Range("A1"))
If Not c Is Nothing Then A = Sheets("Names").Cells(c.Row, 3)
End With
If ActiveSheet.Name <> A Then ActiveSheet.Name = A
End Sub

You will see that I have had to resort to using [ ] around the named range,
which is the only way I could get the code to run.
I tried Range("CompNames") and Range(CompNames) and tried including the
sheet name and the workbook name, but none of those worked.
What am I doing wrong?
Regards - Dave.
 
D

Dave

Hi Jacob,
When I use: With Range("CompNames"), I get:

Runtime error '1004':
Method 'Range' of object '_Worksheet' failed

Regards - Dave.

Jacob Skaria said:
This works

With Range("CompNames")
Set c = .Find(Range("A1"))


End With

--
Jacob


Dave said:
Hi
XL2003
I am having trouble using a named range in a macro, even after refering the
Help.
The named range lives in the active workbook on another sheet.
My code is this:

Private Sub Worksheet_Activate()
With [CompNames]
Set c = .Find(Range("A1"))
If Not c Is Nothing Then A = Sheets("Names").Cells(c.Row, 3)
End With
If ActiveSheet.Name <> A Then ActiveSheet.Name = A
End Sub

You will see that I have had to resort to using [ ] around the named range,
which is the only way I could get the code to run.
I tried Range("CompNames") and Range(CompNames) and tried including the
sheet name and the workbook name, but none of those worked.
What am I doing wrong?
Regards - Dave.
 
J

Jacob Skaria

Check out the exact name of your named range...It seems it is not present in
your active workbook.. Try the below code which should return the
workbookname if the named range is present in the active workbook.

Activeworkbook.Names("Compnames").Parent.name

--
Jacob


Dave said:
Hi Jacob,
When I use: With Range("CompNames"), I get:

Runtime error '1004':
Method 'Range' of object '_Worksheet' failed

Regards - Dave.

Jacob Skaria said:
This works

With Range("CompNames")
Set c = .Find(Range("A1"))


End With

--
Jacob


Dave said:
Hi
XL2003
I am having trouble using a named range in a macro, even after refering the
Help.
The named range lives in the active workbook on another sheet.
My code is this:

Private Sub Worksheet_Activate()
With [CompNames]
Set c = .Find(Range("A1"))
If Not c Is Nothing Then A = Sheets("Names").Cells(c.Row, 3)
End With
If ActiveSheet.Name <> A Then ActiveSheet.Name = A
End Sub

You will see that I have had to resort to using [ ] around the named range,
which is the only way I could get the code to run.
I tried Range("CompNames") and Range(CompNames) and tried including the
sheet name and the workbook name, but none of those worked.
What am I doing wrong?
Regards - Dave.
 
D

Dave

Hi Jacob,
Your line of code runs ok, returning the name of the active workbook. I've
checked the spelling, even pasted it straight from the name box into the
code. As I said, [CompNames] works, but Range("CompNames") doesn't, or any
variant on that I could think of.
Dave.

Jacob Skaria said:
Check out the exact name of your named range...It seems it is not present in
your active workbook.. Try the below code which should return the
workbookname if the named range is present in the active workbook.

Activeworkbook.Names("Compnames").Parent.name

--
Jacob


Dave said:
Hi Jacob,
When I use: With Range("CompNames"), I get:

Runtime error '1004':
Method 'Range' of object '_Worksheet' failed

Regards - Dave.

Jacob Skaria said:
This works

With Range("CompNames")
Set c = .Find(Range("A1"))


End With

--
Jacob


:

Hi
XL2003
I am having trouble using a named range in a macro, even after refering the
Help.
The named range lives in the active workbook on another sheet.
My code is this:

Private Sub Worksheet_Activate()
With [CompNames]
Set c = .Find(Range("A1"))
If Not c Is Nothing Then A = Sheets("Names").Cells(c.Row, 3)
End With
If ActiveSheet.Name <> A Then ActiveSheet.Name = A
End Sub

You will see that I have had to resort to using [ ] around the named range,
which is the only way I could get the code to run.
I tried Range("CompNames") and Range(CompNames) and tried including the
sheet name and the workbook name, but none of those worked.
What am I doing wrong?
Regards - Dave.
 
S

Stefi

Try to add worksheet object qualifier with sheet name hosting the named range
like this:

Worksheets("hostsheet_name").Range("CompNames")

I don't know why, but I had cases when this workaround worked.

--
Regards!
Stefi



„Dave†ezt írta:
Hi Jacob,
Your line of code runs ok, returning the name of the active workbook. I've
checked the spelling, even pasted it straight from the name box into the
code. As I said, [CompNames] works, but Range("CompNames") doesn't, or any
variant on that I could think of.
Dave.

Jacob Skaria said:
Check out the exact name of your named range...It seems it is not present in
your active workbook.. Try the below code which should return the
workbookname if the named range is present in the active workbook.

Activeworkbook.Names("Compnames").Parent.name

--
Jacob


Dave said:
Hi Jacob,
When I use: With Range("CompNames"), I get:

Runtime error '1004':
Method 'Range' of object '_Worksheet' failed

Regards - Dave.

:

This works

With Range("CompNames")
Set c = .Find(Range("A1"))


End With

--
Jacob


:

Hi
XL2003
I am having trouble using a named range in a macro, even after refering the
Help.
The named range lives in the active workbook on another sheet.
My code is this:

Private Sub Worksheet_Activate()
With [CompNames]
Set c = .Find(Range("A1"))
If Not c Is Nothing Then A = Sheets("Names").Cells(c.Row, 3)
End With
If ActiveSheet.Name <> A Then ActiveSheet.Name = A
End Sub

You will see that I have had to resort to using [ ] around the named range,
which is the only way I could get the code to run.
I tried Range("CompNames") and Range(CompNames) and tried including the
sheet name and the workbook name, but none of those worked.
What am I doing wrong?
Regards - Dave.
 
D

Dave Peterson

Just to add to Stefi's advice:

Since you're in the worksheet module (using the worksheet_activate event), an
unqualified range like:

With Range("CompNames")
will refer to the range named CompNames on the worksheet that owns the code.

And your compnames range is on a different sheet.

with worksheets("someothersheetnamehere".range("compnames")
or even specify the workbook:
with thisworkbook.worksheets("someothersheetnamehere".range("compnames")

You could go through the workbook's name collection, too:

with thisworkbook.names("CompNames").referstorange

====================

Ps. I would qualify all the ranges in the code--and I wouldn't use the
Activesheet:

Private Sub Worksheet_Activate()
with thisworkbook.worksheets("Names".range("compnames")
'Me refers to the sheet that owns the code
Set c = .Find(Me.Range("A1"))
'I like the block if/end if
If Not c Is Nothing Then
A = thisworkbook.Sheets("Names").Cells(c.Row, 3)
end if
End With
If me.Name <> A Then me.Name = A
End Sub
Try to add worksheet object qualifier with sheet name hosting the named range
like this:

Worksheets("hostsheet_name").Range("CompNames")

I don't know why, but I had cases when this workaround worked.

--
Regards!
Stefi

„Dave†ezt írta:
Hi Jacob,
Your line of code runs ok, returning the name of the active workbook. I've
checked the spelling, even pasted it straight from the name box into the
code. As I said, [CompNames] works, but Range("CompNames") doesn't, or any
variant on that I could think of.
Dave.

Jacob Skaria said:
Check out the exact name of your named range...It seems it is not present in
your active workbook.. Try the below code which should return the
workbookname if the named range is present in the active workbook.

Activeworkbook.Names("Compnames").Parent.name

--
Jacob


:

Hi Jacob,
When I use: With Range("CompNames"), I get:

Runtime error '1004':
Method 'Range' of object '_Worksheet' failed

Regards - Dave.

:

This works

With Range("CompNames")
Set c = .Find(Range("A1"))


End With

--
Jacob


:

Hi
XL2003
I am having trouble using a named range in a macro, even after refering the
Help.
The named range lives in the active workbook on another sheet.
My code is this:

Private Sub Worksheet_Activate()
With [CompNames]
Set c = .Find(Range("A1"))
If Not c Is Nothing Then A = Sheets("Names").Cells(c.Row, 3)
End With
If ActiveSheet.Name <> A Then ActiveSheet.Name = A
End Sub

You will see that I have had to resort to using [ ] around the named range,
which is the only way I could get the code to run.
I tried Range("CompNames") and Range(CompNames) and tried including the
sheet name and the workbook name, but none of those worked.
What am I doing wrong?
Regards - Dave.
 
S

Stefi

Thanks Dave for the explanation, I didn't realized that being in a worksheet
module may cause this problem, now I know what should I focus on when
encountering this problem again.

--
Regards!
Stefi



„Dave Peterson†ezt írta:
Just to add to Stefi's advice:

Since you're in the worksheet module (using the worksheet_activate event), an
unqualified range like:

With Range("CompNames")
will refer to the range named CompNames on the worksheet that owns the code.

And your compnames range is on a different sheet.

with worksheets("someothersheetnamehere".range("compnames")
or even specify the workbook:
with thisworkbook.worksheets("someothersheetnamehere".range("compnames")

You could go through the workbook's name collection, too:

with thisworkbook.names("CompNames").referstorange

====================

Ps. I would qualify all the ranges in the code--and I wouldn't use the
Activesheet:

Private Sub Worksheet_Activate()
with thisworkbook.worksheets("Names".range("compnames")
'Me refers to the sheet that owns the code
Set c = .Find(Me.Range("A1"))
'I like the block if/end if
If Not c Is Nothing Then
A = thisworkbook.Sheets("Names").Cells(c.Row, 3)
end if
End With
If me.Name <> A Then me.Name = A
End Sub
Try to add worksheet object qualifier with sheet name hosting the named range
like this:

Worksheets("hostsheet_name").Range("CompNames")

I don't know why, but I had cases when this workaround worked.

--
Regards!
Stefi

„Dave†ezt írta:
Hi Jacob,
Your line of code runs ok, returning the name of the active workbook. I've
checked the spelling, even pasted it straight from the name box into the
code. As I said, [CompNames] works, but Range("CompNames") doesn't, or any
variant on that I could think of.
Dave.

:

Check out the exact name of your named range...It seems it is not present in
your active workbook.. Try the below code which should return the
workbookname if the named range is present in the active workbook.

Activeworkbook.Names("Compnames").Parent.name

--
Jacob


:

Hi Jacob,
When I use: With Range("CompNames"), I get:

Runtime error '1004':
Method 'Range' of object '_Worksheet' failed

Regards - Dave.

:

This works

With Range("CompNames")
Set c = .Find(Range("A1"))


End With

--
Jacob


:

Hi
XL2003
I am having trouble using a named range in a macro, even after refering the
Help.
The named range lives in the active workbook on another sheet.
My code is this:

Private Sub Worksheet_Activate()
With [CompNames]
Set c = .Find(Range("A1"))
If Not c Is Nothing Then A = Sheets("Names").Cells(c.Row, 3)
End With
If ActiveSheet.Name <> A Then ActiveSheet.Name = A
End Sub

You will see that I have had to resort to using [ ] around the named range,
which is the only way I could get the code to run.
I tried Range("CompNames") and Range(CompNames) and tried including the
sheet name and the workbook name, but none of those worked.
What am I doing wrong?
Regards - Dave.
 
D

Dave Peterson

It never hurts to qualify ranges/objects--even if you're in a General module and
refering to the activesheet.

I like to use:

dim wks as worksheet
set wks = activesheet

with wks
.range("A1").value = "..."

by using a variable that's declared nicely, I get all that help from the VBE's
intellisense feature. Saves a lot of (my!) typing errors.
Thanks Dave for the explanation, I didn't realized that being in a worksheet
module may cause this problem, now I know what should I focus on when
encountering this problem again.

--
Regards!
Stefi

„Dave Peterson†ezt írta:
Just to add to Stefi's advice:

Since you're in the worksheet module (using the worksheet_activate event), an
unqualified range like:

With Range("CompNames")
will refer to the range named CompNames on the worksheet that owns the code.

And your compnames range is on a different sheet.

with worksheets("someothersheetnamehere".range("compnames")
or even specify the workbook:
with thisworkbook.worksheets("someothersheetnamehere".range("compnames")

You could go through the workbook's name collection, too:

with thisworkbook.names("CompNames").referstorange

====================

Ps. I would qualify all the ranges in the code--and I wouldn't use the
Activesheet:

Private Sub Worksheet_Activate()
with thisworkbook.worksheets("Names".range("compnames")
'Me refers to the sheet that owns the code
Set c = .Find(Me.Range("A1"))
'I like the block if/end if
If Not c Is Nothing Then
A = thisworkbook.Sheets("Names").Cells(c.Row, 3)
end if
End With
If me.Name <> A Then me.Name = A
End Sub
Try to add worksheet object qualifier with sheet name hosting the named range
like this:

Worksheets("hostsheet_name").Range("CompNames")

I don't know why, but I had cases when this workaround worked.

--
Regards!
Stefi

„Dave†ezt írta:

Hi Jacob,
Your line of code runs ok, returning the name of the active workbook. I've
checked the spelling, even pasted it straight from the name box into the
code. As I said, [CompNames] works, but Range("CompNames") doesn't, or any
variant on that I could think of.
Dave.

:

Check out the exact name of your named range...It seems it is not present in
your active workbook.. Try the below code which should return the
workbookname if the named range is present in the active workbook.

Activeworkbook.Names("Compnames").Parent.name

--
Jacob


:

Hi Jacob,
When I use: With Range("CompNames"), I get:

Runtime error '1004':
Method 'Range' of object '_Worksheet' failed

Regards - Dave.

:

This works

With Range("CompNames")
Set c = .Find(Range("A1"))


End With

--
Jacob


:

Hi
XL2003
I am having trouble using a named range in a macro, even after refering the
Help.
The named range lives in the active workbook on another sheet.
My code is this:

Private Sub Worksheet_Activate()
With [CompNames]
Set c = .Find(Range("A1"))
If Not c Is Nothing Then A = Sheets("Names").Cells(c.Row, 3)
End With
If ActiveSheet.Name <> A Then ActiveSheet.Name = A
End Sub

You will see that I have had to resort to using [ ] around the named range,
which is the only way I could get the code to run.
I tried Range("CompNames") and Range(CompNames) and tried including the
sheet name and the workbook name, but none of those worked.
What am I doing wrong?
Regards - Dave.
 
S

Stefi

Thanks for the suggestions!
--
Regards!
Stefi



„Dave Peterson†ezt írta:
It never hurts to qualify ranges/objects--even if you're in a General module and
refering to the activesheet.

I like to use:

dim wks as worksheet
set wks = activesheet

with wks
.range("A1").value = "..."

by using a variable that's declared nicely, I get all that help from the VBE's
intellisense feature. Saves a lot of (my!) typing errors.
Thanks Dave for the explanation, I didn't realized that being in a worksheet
module may cause this problem, now I know what should I focus on when
encountering this problem again.

--
Regards!
Stefi

„Dave Peterson†ezt írta:
Just to add to Stefi's advice:

Since you're in the worksheet module (using the worksheet_activate event), an
unqualified range like:

With Range("CompNames")
will refer to the range named CompNames on the worksheet that owns the code.

And your compnames range is on a different sheet.

with worksheets("someothersheetnamehere".range("compnames")
or even specify the workbook:
with thisworkbook.worksheets("someothersheetnamehere".range("compnames")

You could go through the workbook's name collection, too:

with thisworkbook.names("CompNames").referstorange

====================

Ps. I would qualify all the ranges in the code--and I wouldn't use the
Activesheet:

Private Sub Worksheet_Activate()
with thisworkbook.worksheets("Names".range("compnames")
'Me refers to the sheet that owns the code
Set c = .Find(Me.Range("A1"))
'I like the block if/end if
If Not c Is Nothing Then
A = thisworkbook.Sheets("Names").Cells(c.Row, 3)
end if
End With
If me.Name <> A Then me.Name = A
End Sub

Stefi wrote:

Try to add worksheet object qualifier with sheet name hosting the named range
like this:

Worksheets("hostsheet_name").Range("CompNames")

I don't know why, but I had cases when this workaround worked.

--
Regards!
Stefi

„Dave†ezt írta:

Hi Jacob,
Your line of code runs ok, returning the name of the active workbook. I've
checked the spelling, even pasted it straight from the name box into the
code. As I said, [CompNames] works, but Range("CompNames") doesn't, or any
variant on that I could think of.
Dave.

:

Check out the exact name of your named range...It seems it is not present in
your active workbook.. Try the below code which should return the
workbookname if the named range is present in the active workbook.

Activeworkbook.Names("Compnames").Parent.name

--
Jacob


:

Hi Jacob,
When I use: With Range("CompNames"), I get:

Runtime error '1004':
Method 'Range' of object '_Worksheet' failed

Regards - Dave.

:

This works

With Range("CompNames")
Set c = .Find(Range("A1"))


End With

--
Jacob


:

Hi
XL2003
I am having trouble using a named range in a macro, even after refering the
Help.
The named range lives in the active workbook on another sheet.
My code is this:

Private Sub Worksheet_Activate()
With [CompNames]
Set c = .Find(Range("A1"))
If Not c Is Nothing Then A = Sheets("Names").Cells(c.Row, 3)
End With
If ActiveSheet.Name <> A Then ActiveSheet.Name = A
End Sub

You will see that I have had to resort to using [ ] around the named range,
which is the only way I could get the code to run.
I tried Range("CompNames") and Range(CompNames) and tried including the
sheet name and the workbook name, but none of those worked.
What am I doing wrong?
Regards - Dave.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top