Excel – Using VBA to name a cell name box in Excel

microsoft excelvba

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?

Best Answer

All you need is to change this line:

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

To this:

Set eCell = bCell.Offset(0, 3)
Related Question