This code will only work if you have already assigned Defined Names to the
proper cells in the worksheet:

Sub ColumnLabling()
Dim ColumnIdNumbers(3) As Long
ColumnHeaders = Array("Concat", "Factory", "Plant")
For i = 0 To 2
ColumnIdNumbers(i) = Range(ColumnHeaders(i)).Column
MsgBox (ColumnIdNumbers(i))
Next
End Sub
--
Gary''s Student - gsnu200773


"broro183" wrote:

hi all,

I'm trying to create a quick/short code approach for defining a group of
previously declared (as long) variables. These variables identify the correct
column for various types of data based on single cell named ranges in the
header row and are defined for use later in a macro.

My current working code is
Concat = range("Concat").column
& so on with a separate line of code for each of about 15 different
variables. Is it possible to shorten the code?

Below are my unsuccessful attempts so far, where I have tried to use an
array with the thought of wrapping it in a For Each structure once I get it
working...

Option Explicit
Sub CreatingVariables()
Dim HeaderCols
Dim Concat As Long
dim factory as long
'etc etc...
Dim NamedRange As Name
HeaderCols = Array("Concat", "Factory", "Plant") 'etc etc
'HeaderCols(0) = Range(HeaderCols(0)).Column
'Evaluate(HeaderCols(0)) = Range(HeaderCols(0)).Column
'cstr(evaluate(HeaderCols(0)) = Range(HeaderCols(0)).Column
Cells(4, Concat).Select 'the code currently errors here

'another considered but not yet investigated approach
For Each NamedRange In ActiveWorkbook.Names
With NamedRange
' .Name = right(.RefersTo,
'???
End With
Next NamedRange
''fyi, recorded code when initially creating the named range
'ActiveWorkbook.Names.Add Name:="Factory", RefersToR1C1:="=Data!R4C2"
End Sub

Thanks in advance
Rob