SUMIFS的VBA代码？ [英] VBA code for SUMIFS?

问题描述

我正在尝试编写一个自定义函数，让我从满足x个条件的范围的第一行中检索一个单元格。我想象这将是非常类似于SUMIFS的工作方式，只是简单，因为它不会继续处理后第一场比赛。有没有人知道在VBA中重现SUMIFS（excel 07）功能的代码？

所以，例如如果我有一张excel表：

  WXYZ 
ab 6 1 
ab 7 2 
bb 7 3 
  

我想要能够编写一个函数，它将给出列Z中的值其中列W = a，X = b，Y> = 7（换句话说值2）。

SUMIFS可以大致做到这一点，假设我想要的记录是唯一的，我正在寻找一个数字。为了我的目的，这些假设将不起作用。

解决方案

IMHO ADO不适用于excel工作表功能性能不能轻易在包含数据的工作表上使用）。
这里是一个VBA替代方案：

  
函数MFind（TheRange As Range，ParamArray Tests（）As Variant）As Variant 
 '
'参数是：
'要搜索的范围
'连续列中搜索的值
'所有搜索值除了最后一次使用= 
 '最后一个搜索值使用> = 
'该函数返回范围
'
中的最后一列的值Dim vArr As Variant 
 Dim j As Long 
 Dim k As Long 
 Dim nParams As Long 
 Dim blFound As Boolean 
  vArr = theRange.Value2 
 nParams = UBound（测试） -  LBound（测试）+ 1 
如果nParams> = UBound（vArr，2）则
 MFind = CVErr（xlErrValue）
退出函数
如果
 
对于j = 1到UBound（vArr）
 blFound = True 
对于k = LBound（测试）到nParams  -  2 
如果vArr j，k + 1）测试（k）然后
 blFound = False 
退出
结束如果
下一个k 
如果blFound然后
如果vArr（j，nParams）> =测试（nParams  -  1）然后
 MFind = vArr（j，UBound（vArr，2））
退出
结束如果
结束如果
下一个j 
  
结束功能
 

p>

I'm trying to write a custom function that will let me retrieve a cell from the first row in a range that meets x number of criteria. I imagine this would be very similar to the way SUMIFS works, just simpler in that it doesn't continue processing after the first match.

Does anyone know code to reproduce the SUMIFS (excel 07) function in VBA?

So, for example, if I have a table in excel like:

W X Y Z
a b 6 1
a b 7 2
b b 7 3

I want to be able to write a function that will give me the value in column Z where columns W=a, X=b, Y>=7 (in other words the value 2).

SUMIFS can approximately do this, assuming the record I want is unique and I'm looking to return a number. For my purposes though, those assumptions won't work.

解决方案

IMHO ADO is not suitable for use in excel worksheet functions (poor performance and cannot easily be used on the worksheet containing the data). here is a VBA alternative:

Function MFind(theRange As Range, ParamArray Tests() As Variant) As Variant
'
' Parameters are:
' The Range to be searched
' the values to be searched for in successive columns
' all search values except the last use =
' the last search value uses >=
' the function returns the value from the last column in the range
'
    Dim vArr As Variant
    Dim j As Long
    Dim k As Long
    Dim nParams As Long
    Dim blFound As Boolean
vArr = theRange.Value2
nParams = UBound(Tests) - LBound(Tests) + 1
If nParams >= UBound(vArr, 2) Then
    MFind = CVErr(xlErrValue)
    Exit Function
End If

For j = 1 To UBound(vArr)
    blFound = True
    For k = LBound(Tests) To nParams - 2
        If vArr(j, k + 1) <> Tests(k) Then
            blFound = False
            Exit For
        End If
    Next k
    If blFound Then
        If vArr(j, nParams) >= Tests(nParams - 1) Then
            MFind = vArr(j, UBound(vArr, 2))
            Exit For
        End If
    End If
Next j
End Function

这篇关于SUMIFS的VBA代码？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！