多个范围匹配尝试中的范围级联中的Excel VBA类型不匹配 [英] Excel VBA type mismatch in range concatenation for multiple range match attempt

问题描述

我正在尝试使用Application.match查找在A1:Z1和A2:Z2范围内的值匹配的列.例如，第一行包含不同的水果名称，第二行包含颜色.因此，假设我正在寻找带有蓝色香蕉的色谱柱，则应如下所示:

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

这应该返回C，因为C列代表一个蓝色的香蕉.

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.

我尝试对表达式使用Evaluate，但这也不起作用.

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

任何建议如何做到这一点?

Any suggestions how to do this?

推荐答案

代码中的匹配失败，因为&"运算符仅适用于VBA中的字符串.在Excel工作表中，&"如果将其作为数组公式的一部分输入，则可以联接范围.

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.

对MATCH公式的修改似乎应该在VBA中起作用，也会返回类型不匹配"错误.其中包括通过将两个范围分配给单个范围变量(Range("A1:Z1"，"A2:Z2"))或使用UNION函数(Union("A1:Z1"，"A2:Z2 ))出于相同的目的.使用".Value"限定任何一个都无济于事.

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

关注MATCH表达式(并去除EVALUATE函数所需的多余双引号)，

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

• (nRng1 ="Banana")和(nRng2 ="Blue")是命名范围和两个目标字符串的内容的数组比较.
  
  每个解析为布尔数组{FALSE，FALSE，...，TRUE等}，在包含目标字符串的每个单元格范围内的相对位置中都为TRUE.
  
  
• 双破折号，即-(nRng1 =" Banana)强制将每个布尔数组中的FALSE和TRUE值设为零和一.
  
  
• 然后将两个数组相乘，-(nRng1 ="Banana")*-(nRng2 ="Blue")，生成一个数组，其中两个位置都为"Banana"找到了和"蓝色，在其他位置为零.
  
  请注意，使用这种方法，比较不必仅限于两个.
  
  
• 然后使用MATCH函数 MATCH(1，-(nRng1 ="Banana")*-(nRng2 ="Blue")，0)查找相对位置零和一的数组中的第一个1.
  
  MATCH公式中的最后一个0指定匹配为完全匹配.
  
  
• 由于未找到匹配项，MATCH将返回错误，因此IFERROR将捕获该错误，并返回零.
  
  
• 最后，将最终结果-匹配项的列位置(如果找到一个，否则为0)分配给 mycolumn .

• ( 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.

由于子例程返回单个值，因此您可能需要考虑将其重铸为VBA函数.

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

这篇关于多个范围匹配尝试中的范围级联中的Excel VBA类型不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！