Add name by vba based on cell content

[christobal]

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

 

[William]

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

(e-mail address removed)

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

 

[christobal]

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

 

[William]

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

(e-mail address removed)

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