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/
|