Excel VBA type mismatch in range concatenation for multiple range match attempt

Question

I'm trying to use Application.match to find the column in which a value in range A1:Z1 and A2:Z2 are matched. For example the 1st row contains different fruit names, and the 2nd row contains colours. So, say I am looking for a column with a blue banana, something like this should work:

 mycolumn = Application.Match("Banana" & "Blue", Worksheet("Coloured_Fruit").Range(A1:Z1) & Worksheet("Coloured Fruit").Range(A2:Z2), 0)

In order to match blue banana in the following data sheet:

 A1:banana B1:apple C1:banana D1:orange
 A2:green  B2:blue  C2:blue   D2:green

This should return C, because the C column is the one representing a blue banana.

But I get a type mismatch. The ranges are on a different worksheet to the code that's doing the match. This works fine when I am only trying to match one row, but not for two. A web search suggests the above line should work.

I've tried using Evaluate on the expression, but that doesn't work either.

Any suggestions how to do this?

Answer 1

The MATCH in your code fails because the "&" operator only works on strings in VBA. In Excel worksheets, an "&" can join ranges if it is entered as part of an array formula.

Modifications to the MATCH formula that seem like they should work in VBA also return a "Type Mismatch" error. These include putting the two ranges together by assigning them to a single Range variable (Range("A1:Z1","A2:Z2")), or using the UNION function ( Union("A1:Z1","A2:Z2") ) for the same purpose. Qualifying either of those with ".Value" does not help, either.

The following code does the trick:

  Sub matchit()

     Dim mycolumn As Long
     Dim oRng1 As Range, oRng2 As Range

     With ThisWorkbook.Sheets("Coloured_Fruit")

        Set oRng1 = .Range("A1:Z1")
        Set oRng2 = .Range("A2:Z2")

        .Names.Add Name:="nRng1", RefersTo:=oRng1
        .Names.Add Name:="nRng2", RefersTo:=oRng2

        mycolumn = Evaluate("IFERROR(MATCH(1,--(nRng1=""Banana"")*--(nRng2=""Blue""),0),0)")

        .Names("nRng1").Delete
        .Names("nRng2").Delete

     End With

  End Sub

Focusing on the MATCH expression (and stripping out the extra double-quotes needed for the EVALUATE function),

• ( nRng1 = "Banana" ) and ( nRng2 = "Blue" ) are arrays comparisons of the contents of the named ranges and the two target strings.
  
  Each resolves to an array of booleans {FALSE, FALSE, ..., TRUE, etc.}, with a TRUE in the relative position in the range of each cell that contains the target string.
  
  
• The double-dashes, i.e., "--( nRng1 = "Banana" ) force the FALSE and TRUE values in each of the boolean arrays to zeroes and ones.
  
  
• The two arrays are then multiplied, --( nRng1 = "Banana" ) * --( nRng2 = "Blue" ), producing a single array with ones in the positions where both "Banana" and "Blue" are found, and zeroes elsewhere.
  
  Note that using this approach the comparisons need not be limited to just two.
  
  
• The MATCH function is then used, MATCH( 1, --( nRng1 = "Banana" ) * --( nRng2 = "Blue" ), 0 ), to find the relative position of the first 1 in the array of zeroes and ones.
  
  The final 0 in the MATCH formula specifies that the match be an exact one.
  
  
• Since MATCH will return an error if no match is found, IFERROR catches the error, returning zero in its stead.
  
  
• Finally, the end result - the column position of the match, if one is found, and 0 otherwise -- is assigned to mycolumn.

Since the subroutine returns a single value, you may want to consider recasting it as a VBA function.