向下拖动自动填充以获取用户定义的功能VBA [英] Drag down auto fill for a user defined function VBA

问题描述

我具有以下功能:

Function myFunc() As String

Application.Volatile

Dim i As Long, lTotal As Long
For i = 3 To 10

    If Range("H3").Value = Cells(i, 2).Value And Range("I3").Value < _
    Cells(i, 4).Value And _
    Range("I3").Value >= Cells(i, 3).Value Then

    lTotal = Cells(i, 5).Value + lTotal

    End If

    myFunc = lTotal

Next i

End Function

当我将其向下拖动到列中时，我希望此功能可以自动填充.我该怎么办?

I want this function to be autofill when I drag it down the column. How can I do that?

附件为图像.将Dasboard表视为离散表，在该表中手动输入条目.将日志表视为连续表，其中保留每个日期的每小时记录.仪表板表中的条目将发布到日志表中.我在日志表的输出列中使用此功能.我希望能够将其向下拖动以填充日志表.

Attached is the image. Consider the Dasboard table as a discrete table where manually entries are posted. Consider log table as a continues table where record of every hour for each date is kept. The entries from Dashboard table get posted to the log table. I am using this function in the output column in the log table. I want to be able to drag it down to fill the log table.

推荐答案

为什么要使用UDF?不要重新发明轮子.您可以使用 SUMIFS

Why to use a UDF? Don't re-invent the wheel. You can just do that with SUMIFS

=SUMIFS($E$3:$E$10,$B$3:$B$10,"=" & H3,$C$3:$C$10,"<=" & I3,$D$3:$D$10,">" & I3)

将公式放入J3，然后将其下拉.

Put the formula in J3 and pull it down.

这篇关于向下拖动自动填充以获取用户定义的功能VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！