“连接如果”的VBA用户定义的功能按行 [英] VBA User Defined Function for "Concatenate If" by rows

问题描述

我有一些数据，说明是否已经为特定的网站执行了特定的服务（例如，鸟类调查），每个服务都有是或否。

EG A栏包含站点名称，A，B，C，D和E标题为站点名称，A1那么A2中的A等。

列B在B1中包含鸟类调查，那么B2-B6的是或否 。

同Cit，D和E列中的其他服务同样可以说蝙蝠调查，LVI和土地注册处。

在F中我想连接包含是的每行的服务名称。例如。让我们说B2，C2，D2和E2的值是yes，yes，no和yes，我想要F2包含Bird Survey，Bat Survey。

据了解，excel中没有本机功能可以做到这一点，所以我一直在尝试在VBA中创建一个用户定义的函数。我已经尝试了两种方法：

• 基于两个范围（列名和行是/否 ）到UDF中，然后将它们组合成一个数组，以应用某种查找条件




• ，另一个返回列字母来自一个yes / no范围根据列字母从列名中进行选择。

我还没有得到任何工作。请注意，最终我需要创建一个适用于不同数量服务的UDF，它们不会像这个例子中那样被预先定义。

任何建议

非常感谢提前。

解决方案

我找到这个功能很久以前就发现了一个魅力：

 功能ConcatenateIf（CriteriaRange As Range，条件作为变量，_ 
 ConcatenateRange As Range，可选Separator As String =，）As Variant 
 Dim i As Long 
 Dim strResult As String 
错误GoTo ErrHandler 
如果CriteriaRange.Count<> ConcatenateRange.Count然后
 ConcatenateIf = CVErr（xlErrRef）
退出函数
结束如果
对于i = 1到CriteriaRange.Count 
如果CriteriaRange.Cells（i）。 Value = Condition Then 
 strResult = strResult&分离器ConcatenateRange.Cells（i）.Value 
 End If 
 Next i 
如果strResult<> 然后
 strResult = Mid（strResult，Len（Separator）+ 1）
 End If 
 ConcatenateIf = strResult 
退出函数
 ErrHandler：
 ConcatenateIf = CVErr（xlErrValue）
结束函数
  

给出你的问题，它将被使用如下：

  = ConcatenateIf（B2：E2，yes，$ B $ 1：$ E $ 1，，）
  

初始信用转到这个链接。

希望这样做伎俩！

I have some data of whether or not a particular "service" (e.g. Bird Survey) has been performed for a particular site, with a "yes" or "no" for each service.

E.G.

• Column A contains site names, say A, B, C, D and E with the title "Site Name" in A1 then "A" in A2 etc.

• Column B contains "Bird Survey" in B1, then either a "yes" or "no" for B2-B6.

• Ditto for other services in columns C, D and E, lets say "Bat Survey", "LVI" and "Land Registry" respectively.

• In F I want to concatenate the service names for each row containing a "yes". E.G. lets say the values for B2,C2,D2 and E2 are "yes", "yes", "no" and "yes", I want F2 to contain Bird Survey, Bat Survey.

As I understand it there are no native functions in excel that can do this, and so I've been trying to create a user defined function in VBA. I've tried two approaches

• one based on feeding two ranges (column names and row of "yes/no"'s) into the UDF and then combining these into an array to apply some sort of lookup criteria

• and another returning column letter from one the yes/no range only then selecting from the column names based on column letter.

I've not been able to get either to work though. Note that in the end I need to create a UDF that works for a varying number of services, they won't be pre-defined as in this example.

Any suggestions?

Many thanks in advance.

解决方案

Based upon what you're looking for, I found this function a long time ago and it's worked a charm:

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, _
        ConcatenateRange As Range, Optional Separator As String = ",") As Variant
    Dim i As Long
    Dim strResult As String
    On Error GoTo ErrHandler
    If CriteriaRange.Count <> ConcatenateRange.Count Then
        ConcatenateIf = CVErr(xlErrRef)
        Exit Function
    End If
    For i = 1 To CriteriaRange.Count
        If CriteriaRange.Cells(i).Value = Condition Then
            strResult = strResult & Separator & ConcatenateRange.Cells(i).Value
        End If
    Next i
    If strResult <> "" Then
        strResult = Mid(strResult, Len(Separator) + 1)
    End If
    ConcatenateIf = strResult
    Exit Function
ErrHandler:
    ConcatenateIf = CVErr(xlErrValue)
End Function

Given your question, it would be used as follows:

=ConcatenateIf(B2:E2,"yes",$B$1:$E$1,", ")

Initial credit goes to this link.

Hope this does the trick!!

这篇关于“连接如果”的VBA用户定义的功能按行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！