“连接如果”的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屋!