Having problems creating a custom lookup function in excel. Issue with match and concatenated ranges

Question

Hi there, I'm having some trouble with a large spreadsheet of mine. I bring in a lot of raw data into a data sheet, and then do a number of lookups across the data. Using built in functions I've come up with

=IF(ISNA(INDEX(Data!$L$7:$L$1100,MATCH(Data!$I$2&$B$199&$B29&Data!$J$5,Data!$K$7:$K$1100&Data!$J$7:$J$1100&Data!$I$7:$I$1100&Data!$N$7:$N$1100,0))),"0",INDEX(Data!$L$7:$L$1100,MATCH(Data!$I$2&$B$199&$B29&Data!$J$5,Data!$K$7:$K$1100&Data!$J$7:$J$1100&Data!$I$7:$I$1100&Data!$N$7:$N$1100,0)))

Not pretty! Basically it does the same lookup twice taking 4 variables, and matching them against 4 concatenated arrays, then uses the point as an index for the value I want.

I have 8 of these (slightly different) in each row of 4 sheets and 96 rows in each sheet. Editing them is a pain!

Due to the dataset growing hugely this month, the outer bands (x1100) have been surpassed (lesson learned, large is never enough). Unfortunately limitations of the function won't let me use L:L or anything useful like that.

I've tried rewriting the code as a user defined function where I can feed the 4 variables in, and get the answer back, but have failed dismally at combining the arrays.

I've given the ranges listed above in the original function names to make things easier (and have expanded them to use much wider range values), so I could rewrite all the functions to just use the named ranges, but that still leaves my stuck if I need to change the code.

Here's what I have so far:

    Function Windows_Util(itma As String, env As String)

v = "Windows Server" & env & itma & ""
r = Concat(Range("Utilchassis"))
r = r & Concat(Range("Utilenv"))
r = r & Concat(Range("UtilITMA"))
r = r & Concat(Range("UtilOS"))

m = WorksheetFunction.Match(v, r, 0)

i = WorksheetFunction.Index(Range("Utilavg"), m)


If WorksheetFunction.IsNA(i) Then
    Windows_Util = 0
Else
    Windows_Util = i
End If

End Function


Function Concat(myRange As Range, Optional myDelimiter As String)

Dim r As Range

Application.Volatile

For Each r In myRange
   If Len(r.Text) Then
       Concat = Concat & IIf(Concat <> "", myDelimiter, "") & r.Text
   End If
Next

End Function

This doesn't work! Not only does it concatenate incorrectly (each range is concatenated separately, not combined row by row), it doesn't like some type in one of the queries. (debugging these things is not easy as the function actually completes (it doesn't have any syntax errors in it), so there are no built in step throughs I can use.

Any help greatly appreciated.

Hopefully I've given enough details to make sense of what I'm trying to do.

Cheers,

Steve

Answer 1

How about:

r = Range("Utilchassis,Utilenv,UtilITMA,UtilOS")

That would seem to achieve the same as your Concat function

Answer 2

I'm not sure exactly how you would like the Ranges to be concatinated, but I think you should have a look at the Application.Union(Range, Range, ...) method.

In the example from the article:

Set bigRange = Application.Union(Range("Range1"), Range("Range2"))

See also the Article in the Daily Dose of Excel by Tushar Mehta where he describes the following custom Union(Range, Range) method that can better handle 'Nothing' (null) inputs:

Function Union(Rng1 As Range, Rng2 As Range) As Range
    If Rng1 Is Nothing Then
        Set Union = Rng2
    ElseIf Rng2 Is Nothing Then
        Set Union = Rng1
    Else
        Set Union = Application.Union(Rng1, Rng2)
    End If
End Function

If, instead, you are going to use string concatination of the range addresses, then you have to add the commas, as Barrowc is showing in his post. For example Range("MyRange1, MyRange2") would union the two ranges named "MyRange1" and "MyRange2", whereas a space separator without a comma, such as Range("MyRange1 MyRange2") would return the INTERSECTION (overlap) of these two ranges.

However, I would recommend avoiding the concatenation of string addresses and use the Application.Union(Range, Range) method instead.

Hope this helps...