Refer to a named range
 

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.

Refer to a named range
 

This works

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


End With

--
Jacob


"Dave" wrote:

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.

Refer to a named range
 

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

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

Regards - Dave.

"Jacob Skaria" wrote:

This works

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


End With

--
Jacob


"Dave" wrote:

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.

Refer to a named range
 

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" wrote:

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

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

Regards - Dave.

"Jacob Skaria" wrote:

This works

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


End With

--
Jacob


"Dave" wrote:

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.

Refer to a named range
 

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" wrote:

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" wrote:

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

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

Regards - Dave.

"Jacob Skaria" wrote:

This works

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


End With

--
Jacob


"Dave" wrote:

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.

Refer to a named range
 

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" wrote:

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" wrote:

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

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

Regards - Dave.

"Jacob Skaria" wrote:

This works

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


End With

--
Jacob


"Dave" wrote:

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.

Refer to a named range
 

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("compnam es")
or even specify the workbook:
with thisworkbook.worksheets("someothersheetnamehere".r ange("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.

"Jacob Skaria" wrote:

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" wrote:

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

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

Regards - Dave.

"Jacob Skaria" wrote:

This works

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


End With

--
Jacob


"Dave" wrote:

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

Refer to a named range
 

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("compnam es")
or even specify the workbook:
with thisworkbook.worksheets("someothersheetnamehere".r ange("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.

"Jacob Skaria" wrote:

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" wrote:

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

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

Regards - Dave.

"Jacob Skaria" wrote:

This works

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


End With

--
Jacob


"Dave" wrote:

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
.

Refer to a named range
 

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.

Stefi wrote:

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("compnam es")
or even specify the workbook:
with thisworkbook.worksheets("someothersheetnamehere".r ange("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.

"Jacob Skaria" wrote:

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" wrote:

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

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

Regards - Dave.

"Jacob Skaria" wrote:

This works

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


End With

--
Jacob


"Dave" wrote:

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
.


--

Dave Peterson

Refer to a named range
 

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.

Stefi wrote:

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("compnam es")
or even specify the workbook:
with thisworkbook.worksheets("someothersheetnamehere".r ange("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.

"Jacob Skaria" wrote:

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" wrote:

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

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

Regards - Dave.

"Jacob Skaria" wrote:

This works

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


End With

--
Jacob


"Dave" wrote:

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
.


--

Dave Peterson
.