Naming Cells using VBA

  • Thread starter Thread starter Kattoor
  • Start date Start date
K

Kattoor

I am trying to name the cells through Macro.
I want the program to work as follows.
1. Go through eac cells in column A, (first column only)
2. If the program finds a specific text which I am looking for, what
want to do is to name the cell using the text appearing inside tha
cell. For example say I am looking for the word "Product" Now I foun
in say cell A4, the word "Product 430". I know from the program tha
this particular cell contains the word "Product". So I need to nam
this cell as "Product 430".

I am trying to use the following command where CellName is the variabl
in which I have stored the value inside that cell.

ActiveWorkbook.Names.Add name:=CellName, RefersTo:="="
Selection.Address()

But it is giving problem. Can anyone help me.
Thanks very much.
Ton
 
One way:

Const csName As String = "Product"
Dim rCell As Range
For Each rCell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With rCell
If .Text Like csName & "*" Then _
.Name = Application.Substitute(.Text, " ", "")
End With
Next rCell

Names can't have spaces, so I eliminated them - you can convert them to
underscores or other characters.
 
Back
Top