Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can VBA use array constants
This question follows from
<http://groups.google.com/g/8077ff07/...18cd6d29d06f79 c. In VBA, I want to lookup a column number which refers to a London Underground line name. My first version using VLOOKUP was: Dim colIndex ' Translation of Line to a column number ActiveCell.Formula = "=VLOOKUP(""" & Line & """," _ & "{""Bakerloo"",5;""Central"",6, ...},2,TRUE)" colIndex = ActiveCell.Value My second version using MATCH used this line: ActiveCell.Formula = "=4+MATCH(""" & Line & """," _ & "{""Bakerloo"",""Central"", ...}, 1)" (With both those versions both "Central" and "Central line" match.) My third version using the VBA find function used the following code: Dim lines As Range ' at module level Set lines = Range("E1:O1") ' in a public subroutine colIndex = lines.Find(Line).Column (With this version, "Central line" (unsurprisingly) does not match.) Is it possible to use an array constant for the range in such code? If so, how? I got a 1004: "Method 'Range' of object '_Global' failed" with Set lines = Range(Array("Bakerloo", "Central", "Circle", ...)) As it happens, I am happy with the third method. I am not happy with my ignorance. ;) -- Walter Briscoe |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Constants | Excel Worksheet Functions | |||
unions, intersections or array constants | Excel Worksheet Functions | |||
not sure on constants | Excel Programming | |||
Declaring array constants at module level | Excel Programming |