Refer to a named range

[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.

 

[Jacob Skaria]

This works

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


End With

 

[Dave]

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.

 

[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

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.

 

[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.

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.

 

[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.

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.

 

[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.

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.

 

[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.

 

[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.

 

[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.