View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.programming
burl_rfc_h burl_rfc_h is offline
external usenet poster
 
Posts: 13
Default Creating a Dynamic Named Range Using Sheet Name and Column Header

Tom,

I substituted the actual sheet name in the following code, the formula
is now correct, but the range name only came up with "_MSG", in cell A2
I had the following text "test here", so therefore the range name
should have been "test_here_MSG", (I used to quotation marks in the
preceeding example just to hightlight the area of concern)

Dim sName As String

sName = Cells(1, ActiveCell.Column).Value
ThisWorkbook.Names.Add Name:=sName & "_MSG", _
RefersTo:="=OFFSET(" & sName & "MSG!" & Cells(2, _
ActiveCell.Column).Address & _
",1,0,COUNTA(" & sName & "MSG!" & _
ActiveCell.EntireColumn.Address & ")-1,1)"

Thanks
Burl