Incrementally name a range of cells

[ninad7]

I saw this on a post on Mr. Excel forum.


Sub NameRange()


Dim RowRange As Long

For RowRange = 1 To 15

Cells(RowRange, "A").Name = "Results" & RowRange

Next


End Sub


I'd like to learn how this can be converted into a function with the following parameters

* numStartRow : the row position from where to begin

* numEndRow : the row position where to end and numEndRow >= numStartRow

* varColumnHeader : a valid XL Column header A,B,C,....XFD

* varRangeNameIdentifier : A Text based Range Name to which suffix 1,2,3,....will be added.


The purpose is to pass to this function values such as NameRange(1,5,"B","EmployeeID"). The execution of the code should name cells from B1:B5 as EmployeeID1, EmployeeID2, ..., EmployeeID5


Help appreciated.


TIA.


Ninad.

 

[NARAYANK991]

Hi Ninad ,


I don't think what you have described is possible , since a UDF is not supposed to affect multiple cells.


However , what you can do is have this as a procedure as follows :

[pre]

Sub NameRange(numStartRow, numEndRow, varColumnHeader, varRangeNameIdentifier)

Dim RowRange As Long
For RowRange = numStartRow To numEndRow
Cells(RowRange, varColumnHeader).Name = varRangeNameIdentifier & RowRange
Next

End Sub
and call this from within another procedure as follows :

Public Sub Create_Named_Ranges()
Call NameRange(1, 5, "B", "EmployeeID")
End Sub

[/pre]
This will result in 5 named ranges EmployeeID1 , EmployeeID2 , ... being created , referring to $B$1 , $B$2 , ....


Narayan

 

[ninad7]

@NarayanK991:


Now I know why I was getting an error. Thank you for your help.


Regards,


Ninad.