Add name by vba based on cell content
 

Have need of a vba snippet which will pass the contents of th
activecell as the "name" of a named range with reference to the
4 columns to the right and 5 rows below the activecell as the rang
area
Any ideas

--
Message posted from http://www.ExcelForum.com

Add name by vba based on cell content
 

I'm assuming that if the current cell is D7 and contains the word "bob" then
you want to name the range E8:H12 "bob"

Sub test()
Dim r As Range, s As String
With ActiveSheet
Set r = ActiveCell
s = r.Text
Set r = .Range(r.Offset(1, 1), r.Offset(5, 4))
ThisWorkbook.Names.Add Name:=s, RefersTo:=r
End With
End Sub

--
XL2002
Regards

William



"christobal " wrote in message
...
| Have need of a vba snippet which will pass the contents of the
| activecell as the "name" of a named range with reference to the
| 4 columns to the right and 5 rows below the activecell as the range
| area
| Any ideas !
|
|
| ---
| Message posted from http://www.ExcelForum.com/
|

Add name by vba based on cell content
 

The cell contains the CONCATENATED result of 2 adjacent cells
1. Height represented as number i.e. 55
2. Date value i.e. 12/12/2004
3. cell result = "55 12/12/2004"

When using insert name define from the commandbar the resulting tex
is converted to
_55_12_12_2004

The code "ThisWorkbook.Names.Add Name:=s, RefersTo:=r"
on this particular cell returns
run-time error 1004
That name is not valid

If there anyway to overcome this problem

--
Message posted from http://www.ExcelForum.com

Add name by vba based on cell content
 

Assuming cell B7 contains 55, C7 contains 12/12/2004 and D7 is the active
cell.......

Sub test()
Dim r As Range, s As String
With ActiveSheet
Set r = ActiveCell
s = "_" & r.Offset(0, -2) & "_" & Format(r.Offset(0, -1), "dd_mmm_yy")
Set r = .Range(r.Offset(1, 1), r.Offset(5, 4))
ThisWorkbook.Names.Add Name:=s, RefersTo:=r
End With
End Sub


--
XL2002
Regards

William



"christobal " wrote in message
...
| The cell contains the CONCATENATED result of 2 adjacent cells
| 1. Height represented as number i.e. 55
| 2. Date value i.e. 12/12/2004
| 3. cell result = "55 12/12/2004"
|
| When using insert name define from the commandbar the resulting text
| is converted to
| _55_12_12_2004
|
| The code "ThisWorkbook.Names.Add Name:=s, RefersTo:=r"
| on this particular cell returns
| run-time error 1004
| That name is not valid
|
| If there anyway to overcome this problem.
|
|
| ---
| Message posted from http://www.ExcelForum.com/
|