VBA: Assigning Multiple Ranges to Variable Names

[flipdazed]

Hello there,

In my macro I basically want to assign various column ranges to variable names so that I can call them later.
I am often finding that the layout of the data I work with changes and so I need to use range variables defined by column titles to be able to consistently use the correct columns in my code.

I am unsure where my error is in the following code. The aim of it is to use two set up arrays of column titles in string format that I can search for and variable names for each column once they're found.

The loop searches for each column and then assigns the corresponding variable name to it for future reference.

I think my error is in my declarations and a general muddling of variable types but I don't have enough VBA knowledge to untangle the mess!

If anyone can help it would be greatly appreciated as I think this is a topic a lot of people could do with help on.

[COLOR="Blue"]Sub[/COLOR] Test()

    [COLOR="Blue"]Dim [/COLOR]SalesDoc    [COLOR="blue"]As [/COLOR]Range, NetValue  [COLOR="blue"]As [/COLOR]Range
    [COLOR="Blue"]Dim [/COLOR]CreatedOn   [COLOR="blue"]As [/COLOR]Range, DocCurr   [COLOR="blue"]As [/COLOR]Range
    [COLOR="Blue"]Dim [/COLOR]CreatedBy   [COLOR="blue"]As [/COLOR]Range, SalesOrg  [COLOR="blue"]As [/COLOR]Range
    [COLOR="Blue"]Dim [/COLOR]SalesGrp    [COLOR="blue"]As [/COLOR]Range, SalesOff  [COLOR="blue"]As [/COLOR]Range
    [COLOR="Blue"]Dim [/COLOR]PODate      [COLOR="blue"]As [/COLOR]Range, FindRng   [COLOR="blue"]As [/COLOR]Range
    [COLOR="Blue"]Dim [/COLOR]ColArray    [COLOR="blue"]As [/COLOR]Variant, TitleArray [COLOR="blue"]As [/COLOR]Variant
    [COLOR="Blue"]Dim [/COLOR]i           [COLOR="blue"]As [/COLOR]Integer

[COLOR="green"]'---------Set up Arrays-------------[/COLOR]
    ColArray = Array("Sales Document", "Created On", "Created by", "Net Value", _
                    "Doc. Currency", "Sales Org.", "Sales Group", "Sales Office", _
                    "PO date")
    TitleArray = Array(SalesDoc, CreatedOn, CreatedBy, NetValue, DocCurr, _
                        SalesOrg, SalesGrp, SalesOff, PODate)
[COLOR="Green"]'-----------------------------------[/COLOR]

[COLOR="green"]'Loop to assign column ranges to each variable[/COLOR]
    [COLOR="Blue"]For [/COLOR]i = 0 [COLOR="blue"]To [/COLOR]8
        [COLOR="blue"]Set [/COLOR]FindRng = Range("A1:Z1").Find(ColArray(i))

[COLOR="Green"]'Error comes up here....[/COLOR]
        [COLOR="blue"]Set [/COLOR]TitleArray(i) = Range(FindRng, FindRng.End(x1down))
        [COLOR="blue"]If[/COLOR] FindRng [COLOR="blue"]Is Nothing Then[/COLOR]
            MsgBox ColArray & " column title was not found."
           [COLOR="blue"] Exit Sub[/COLOR]
        [COLOR="blue"]End If[/COLOR]
    [COLOR="blue"]Next [/COLOR]i

[COLOR="blue"]End Sub[/COLOR]

 

[Andrew Poulsom]

Your code works for me if I change x1down to xlDown in this line:

Set TitleArray(i) = Range(FindRng, FindRng.End(xlDown))

But note that the code is not assigning the ranges to your variables SalesDoc, CreatedOn etc. Rather it is assigning them to the elements of TitleArray. You have to be explicit about the names of variables - you can't use the text in an array or build them up with concatenation.

 

[flipdazed]

ah... Thanks Andrew, didn't realise it was an "L" that was needed!

Does this mean I can scrap all the names of my variables and just use the array "TitleArray" as a an array of column ranges that I can call when I need to use them?

 

[Andrew Poulsom]

ah... Thanks Andrew, didn't realise it was an "L" that was needed!

Does this mean I can scrap all the names of my variables and just use the array "TitleArray" as a an array of column ranges that I can call when I need to use them?

xl is short for Excel. You can use the items in the array, but you will need to remember the position of each range within it.