Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
shortening code by defining variables using an array?
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modify code for multiple sheets-Help defining array | Excel Programming | |||
Defining Variables with Same Name in Different Worksheets | Excel Discussion (Misc queries) | |||
Multiple Criteria - Shortening Code | Excel Programming | |||
VBA defining variables | Excel Discussion (Misc queries) | |||
Help with shortening/cleaning some code please | Excel Programming |