PDA

View Full Version : Solved: Dynamic Range based on Heading



JimS
12-02-2011, 01:25 PM
I have a template that I use that has several Range Names. I import the data source via a copy/paste macro.

Every once in a while the source of my data file swaps a few of the columns of data around - it's the same data just in a different columns once in a while.

Is it possible to create the dynamic range names so that it uses the Heading?

Here's an example of one of the Range Names for the "Date" range (which normally is in Column - T, but sometimes it is swapped with a different column):

=OFFSET('Main_Data1'!$T$2,0,0,COUNTA('Main_Data1'!$A:$A)-1,1)

Thanks for any help...

JimS

Paul_Hossler
12-02-2011, 02:57 PM
I'd do something like this


Sub test()
Call AddNameFromHeader("eee", "NameEEEE")
Call AddNameFromHeader("aaa", "NameAAAA")
MsgBox [NameAAAA].Address
MsgBox [NameEEEE].Address


End Sub

Sub AddNameFromHeader(sHeader As String, sName As String)
Dim iHeader As Long
Dim sRefersTo As String
iHeader = 0
On Error Resume Next
iHeader = Application.WorksheetFunction.Match(sHeader, ActiveSheet.Rows(1), 0)
On Error GoTo 0

If iHeader = 0 Then Exit Sub

On Error Resume Next
ActiveWorkbook.Names(sName).Delete
On Error GoTo 0

With ActiveSheet
sRefersTo = "=OFFSET("
sRefersTo = sRefersTo & "'" & .Name & "'!" & .Cells(2, iHeader).Address(True, True) & ",0,0,"
sRefersTo = sRefersTo & "COUNTA("
sRefersTo = sRefersTo & "'" & .Name & "'!" & .Cells(1, iHeader).EntireColumn.Address(True, True) & ")-1,1)"

.Parent.Names.Add Name:=sName, RefersTo:=sRefersTo
End With
End Sub




Paul

JimS
12-02-2011, 07:23 PM
Paul,

Thanks for the response - can you give me a hint at how this works so I know what I need to modify?

Thanks again...

JimS

Paul_Hossler
12-03-2011, 05:20 AM
Guess I was a little terse ...


All I did was a sub (AddNameFromHeader) with 2 parameters: the first is the column header in row 1 to look for, and the second is the name to give the range for the cells in that column, starting in row 2 for the COUNTA number of rows

Very similar to what I think you were doing, just automated a bit

So in the example:

Call AddNameFromHeader("eee", "NameEEEE")


if X1 = 'eee' and there is data from X1 to X100, the sub will name X2:X100 as 'NameEEEE'


Like I said, that was what I think you were doing

Paul

JimS
12-03-2011, 05:44 AM
Paul,

Perfect, thanks - this is just what I was looking for.

JimS