shortening code by defining variables using an array?

[broro183]

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

 

[Gary''s Student]

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

 

[broro183]

Thanks Gary's Student, yes, the names are defined in the ss. Your suggestion
is going in the right direction but it's not quite what I'm hoping for yet
(as far as readibility of later code goes - see *).

Instead of identifying the column using the index number from the array in
"ColumnIdNumbers(0)" to equal 1 (for the first column) is there any way that
I can end up with "Concat = 1" for use in code such as...?

* examples of possible uses:
dim Concat as Long
....
..autofilter Field:=Concat, Criteria1:="=xyz"
'cf .autofilter Field:=ColumnIdNumbers(0), Criteria1:="=xyz"
or

with
range(cells(5,Concat),cells(lastrow,Concat)).SpecialCells(xlCellTypeVisible)
'format etc &
..value = .value
end with

I'm sorry I can't explain this very well - I guess I'm really after a
"translation", from the indexed array variable into the single Long variable
which has the same name as the defined name.
The question came about b/c I've been optimising someone else's code & my
use of named ranges has evolved from the original code which was looping
through each column until it found the word Concatenate in the header row &
asigning the column number at that point. I initially changed this to use a
Find function rather than a loop and when I realised that named ranges in a
Template file would be quicker still I ended up with the below:
"Concat = range("Concat").column"
"Factory = range("Factory").column"
etc etc
when I looked at this & saw the duplication on each side of the equals sign
I started wondering if it could be shortened even more but I understand if
that's not possible & will stick with my 15 lines of code...

thanks
Rob