Возникли проблемы с созданием пользовательской функции поиска в Excel. Проблема с совпадающими и конкатенированными диапазонами

Как насчет:

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

Казалось бы, достигается то же, что и ваша функция Concat

Я не совсем уверен, как вы хотите, чтобы диапазоны были объединены, но я думаю, вам стоит взглянуть на Application.Union (Range, Range, ...) метод.

В примере из статьи:

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

См. Также Статья Тушара Мехты в Daily Dose of Excel, где он описывает следующий настраиваемый метод Union (Range, Range), который может лучше обрабатывать входные данные Nothing (null):

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

Если вместо этого вы собираетесь использовать конкатенацию строк адресов диапазона, вам нужно добавить запятые, как показывает Барроус в своем сообщении. Например, Range("MyRange1, MyRange2") объединит два диапазона с именами «MyRange1» и «MyRange2», тогда как разделитель пробела без запятой, такой как Range("MyRange1 MyRange2"), вернет ПЕРЕСЕЧЕНИЕ (перекрытие) этих двух диапазонов.

Однако я бы рекомендовал избегать объединения строковых адресов и вместо этого использовать метод Application.Union(Range, Range).

Надеюсь это поможет...

Функцию ПОИСКПОЗ может быть сложно использовать в VBA, когда существует более одного критерия для сопоставления. То, что делается на листе - критерии соответствия, объединенные из разных ячеек с равным количеством связанных диапазонов, в обоих случаях с использованием оператора & - недостижимо в VBA, потому что оператор "&" VBA принимает только строки в качестве аргументов и потому что другие способы объединения диапазонов в VBA, функция Union и назначение с помощью нескольких аргументов адреса для Range, похоже, не создают массив, который будет работать с функцией сопоставления VBA.

Однако можно добиться того же эффекта, создав формулу рабочего листа, объединяющую ПОИСКПОЗ и сравнения массивов, а затем выполняя формулу с помощью функции VBA Evaluate. См. Мой ответ на этот ТАК вопрос, чтобы узнать, как работает этот подход.

Следующий код по существу повторно реализует исходную формулу рабочего листа, предоставленную Стивом, вне зависимости от ранее названных диапазонов на листе или значений критериев, указанных в коде (в отличие от конкретных ячеек на листе). Он написан как подпрограмма, но может быть легко преобразован в пользовательскую функцию.

  Sub x4match()

    Dim adr1 As String, adr2 As String, adr3 As String, adr4 As String
    Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, Rng4 As Range
    Dim name1 As String, name2 As String, name3 As String, name4 As String
    Dim idx As Variant
    Dim resultRng As Range
    Dim result As Variant

    With ThisWorkbook.Worksheets("Data")

       adr1 = "I2"
       adr2 = "B199"
       adr3 = "B29"
       adr4 = "J5"

       Set Rng1 = .Range("K7:K1100")
       Set Rng2 = .Range("J7:J1100")
       Set Rng3 = .Range("I7:I1100")
       Set Rng4 = .Range("N7:N1100")
       Set resultRng = .Range("L7:L1100")

       .Names.Add Name: = "name1", RefersTo:=Rng1
       .Names.Add Name: = "name2", RefersTo:=Rng2
       .Names.Add Name: = "name3", RefersTo:=Rng3
       .Names.Add Name: = "name4", RefersTo:=Rng4

       idx = Evaluate("IFERROR( MATCH(1, --(" & adr1 & "=name1) * --(" & _
                     adr2 & "=name2) * --(" & adr3 & "=name3) * --(" & _ 
                     adr4 & "=name4), 0), 0)")

       If idx <> 0 Then
          result = .Cells(resultRng.Row + idx - 1, resultRng.Column).Value
       End If

      .Names("name1").Delete
      .Names("name2").Delete
      .Names("name3").Delete
      .Names("name4").Delete

    End With

  End Sub