need help with changing cell name in worksheet using VBA
need help with changing cell name in worksheet using VBA
(OP)
Using VBA (and Excel 2007), I have a problem with a line where I want to change the cell referenced by a name.
All I am trying to do is assign an existing name to a new cell. In simplest form:
Sub Red()
Dim WB As Workbook
Dim WS As Worksheet
Set WB = ActiveWorkbook
Set WS = Worksheets("Sheet1")
WS.Range("myCell").Offset(4, 0).Name = "myCell"
End Sub
This version works just fine. I can run it all day long.
The problem is if I change the line
WS.Range("myCell").Offset(4, 0).Name = "myCell"
To
WB.WS.Range("myCell").Offset(4, 0).Name = "myCell"
In the latter case I get the error "Object doesn't support this property or method"
In the original case, it is implicit that the range where the name is to be changed is in the active workbook. But I want the operation to act on another (open) workbook, one that is not active. Thus my use of setting variable WB to "some" workbook. For this sample I even set WB to the active workbook...and I still get the error!
Can someone explain this please? Thank you.
All I am trying to do is assign an existing name to a new cell. In simplest form:
Sub Red()
Dim WB As Workbook
Dim WS As Worksheet
Set WB = ActiveWorkbook
Set WS = Worksheets("Sheet1")
WS.Range("myCell").Offset(4, 0).Name = "myCell"
End Sub
This version works just fine. I can run it all day long.
The problem is if I change the line
WS.Range("myCell").Offset(4, 0).Name = "myCell"
To
WB.WS.Range("myCell").Offset(4, 0).Name = "myCell"
In the latter case I get the error "Object doesn't support this property or method"
In the original case, it is implicit that the range where the name is to be changed is in the active workbook. But I want the operation to act on another (open) workbook, one that is not active. Thus my use of setting variable WB to "some" workbook. For this sample I even set WB to the active workbook...and I still get the error!
Can someone explain this please? Thank you.
Talk To Other Members
RE: need help with changing cell name in worksheet using VBA
When you Set WS, you must fully qualify to the appropriate workbook.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: need help with changing cell name in worksheet using VBA
Skip, I expect that you mean I should say: Set WS = WB.Worksheets("Sheet1") in lieu of Worksheets("Sheet1"). That doesn't seem to work though.
RE: need help with changing cell name in worksheet using VBA
Sub Red()
Dim WS As Worksheet
Set WS = ActiveWorkbook.Worksheets("Sheet1")
WS.Range("myCell").Offset(4, 0).Name = "myCell"
End Sub
RE: need help with changing cell name in worksheet using VBA
RE: need help with changing cell name in worksheet using VBA
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
[IMG http://tinyurl.com/7ofakss]
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers
Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
There is a homework forum hosted by engineering.com: http://www.engineering.com/AskForum/aff/32.aspx
RE: need help with changing cell name in worksheet using VBA
Why do you need the same Named Range on multiple sheets in a sheet scope? What's the purpose?
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: need help with changing cell name in worksheet using VBA
Skip: I have a workbook with multiple copies of similar tables. I want to "tag" a particular cell in each table, and this tag will move as data changes are made to the tables. It works just fine with the named ranges as limited to worksheet scope. I want to "move" the tag programmatically.
RE: need help with changing cell name in worksheet using VBA
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: need help with changing cell name in worksheet using VBA
WS.Names.Add Name:="myCell", RefersToR1C1:="=Sheet1!R29C3"
Skip: The exact code in context is in my first post.
RE: need help with changing cell name in worksheet using VBA
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: need help with changing cell name in worksheet using VBA
http://dailydoseofexcel.com/archives/2015/05/29/gl...
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: need help with changing cell name in worksheet using VBA
CODE
With WS .Names.Add Name:="myCell", RefersTo:="='" & .Name & "'!" & .Range("myCell").Offset(4, 0).Address(TRUE,TRUE)Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: need help with changing cell name in worksheet using VBA
I have my routine going with the alternative command to the effect of: WS.Names.Add Name:="myCell", RefersToR1C1:="=Sheet1!R29C3"
It's a bit mindboggling to me that such a command as: WS.Range("myCell").Offset(4, 0).Name = "myCell" works but doesn't seem to be able to be generalized.