2

I would like to iterate through rows in a worksheet and assign the name boxes of one column (the E column) to the value of that respective row in another column (the B column).

I would like to accomplish it this way because the principle will stay the same for an addendum that will be made to this script for long term maintenance. For example, if I were to do it manually, I would activate cell "E2" and click the name box, and type in "Coil_Width", which is the value of cell "B2". This properly adds the name to the name manager and I can dynamically change the value by typing something in the formula bar for that cell. I've tried assigning via the eCell.Name = (etc) property, but it seems it doesn't work the same for cells as it does for the range property because I get an application defined error (1003).

Also, using the standard 

ActiveWorkbook.Names.Add Name:=bCell.Value, RefersTo:= eCell.Address()

just adds the name to the name manager and sets the RefersTo to "$E$2" or whatever cell (which is correct I think), but the value gets set to "E2" as well (and cannot be changed even when manually typing a value into the formula bar for that cell. In addition, the name box for that cell still shows as being "E2".

Here is some example code:

For Each bCell In thisWkSht.Range("B:B").Cells
'For Each bCell In thisWkSht.Range("B:B:) - this is the changed line
If bCell.Value = "" Then
    'Do Nothing
ElseIf bCell.Value = "Variables" Then
    'Do Nothing
    'This allows me to skip the first row without much hassle
Else
    Set eCell = bCell.Offset(0, 3).Cells 
    'Set eCell = bCell.Offset(0, 3) - this is the other changed line
    eCell.Name = bCell.Value ' This line now correctly assigns the value from my
                             ' B row and column to the name of my E row cell

End If
Next

My results show that I can correctly access the bcell.Value property, but it breaks during the bCell.Name.Name. It seems to me that I should just be able to do something like eCell.Name = bCell.Value or something similar, but I've yet to receive anything other than an application defined error.

I'm currently sticking with the cells method because I'm not sure how to address a changing range selection in a For Each statement; i.e. I can understand how to address my bCell to be over the entire Range("B:B"), but I wouldn't know how to set my eCell so that the range it referred to was actually the range of the bCell offset by three columns.

Am I missing something obvious about these properties?

Improve this question
6
  • I'm not a VBA expert, but I normally use Range instead of cells to save some headaches. Have you tried to work with Ranges rather than cells (ie: ActiveWorkBooks.Names.Add Name:=Range("B2").value, RefersToR1C1:=Range("E2"))?
    – shinjijai
    Commented Jan 13, 2014 at 15:12
  • 1
    You can answer your own question, and if you found the solution yourself, it is in fact advisable to do so.
    – That Brazilian Guy
    Commented Jan 13, 2014 at 15:37
  • So you're saying there is a way to mark the question as answered? I tried looking back in the edit section, but I did not see a way to flag it as solved or anything. Where do I look to do that?
    – NateDawg
    Commented Jan 13, 2014 at 19:03
  • 2
    You added the answer as edit to the question. Instead, on Stack Exchange sites, you should add the answer in a separate post below. There's absolutely nothing wrong with answering your own question as described here. You can then (in a day or so) click the check mark next to that answer to mark the question solved.
    – Daniel Beck ♦
    Commented Jan 13, 2014 at 19:14
  • Grab your answer from superuser.com/posts/701056/revisions and clean it up. Then post it as an answer and accept it.
    – random ♦
    Commented Jan 14, 2014 at 17:26

1 Answer 1

Reset to default
0

All you need is to change this line:

Set eCell = bCell.Offset(0, 3).Cells

To this:

Set eCell = bCell.Offset(0, 3)
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .