时间戳

首页 > 解决方案 > 如果我在此类单元格上使用 VBA 函数，如何获取单元格地址？

excel - 如果我在此类单元格上使用 VBA 函数，如何获取单元格地址？

问题描述

我想在 Excel 表中使用 VBA 函数 ScopeSum()，该函数检查同一行上的“1”值，然后对相关标题的值求和。
“SopeH”被命名为标题范围。
对于 100 行，我必须在同一列（下例中的列“P”）上使用此函数。
如果我复制函数单元格并填充所有行，结果将作为第一个单元格，但如果我编辑它，它工作正常。

Function ScopeSum() As String
Dim i As Integer
Dim j As Long
Dim rng As Range
Dim cur_rng As Range
Dim ScopeText As String
Dim cell As Variant
Set rng = Range("ScopeH")
j = Range(ActiveCell.Address).Row

Set cur_rng = Range("ScopeH").Offset(j - 2, 0)
i = 0
ScopeText = ""
For Each cell In cur_rng.Cells
    i = i + 1
    If UCase(cell.Value) = 1 Then ScopeText = ScopeText & ", " & Application.WorksheetFunction.Index(rng, 1, i)
Next
ScopeSum = ScopeText
End Function

表格
表格

刷新页面后
刷新页面后

标签： excelvba

解决方案

确保将数据和标题范围作为参数提交，以便 UDF（用户定义函数）适用于任何数据范围并取决于数据范围。否则，如果数据更改，您的公式将不会自动更新。

Option Explicit

Public Function ScopeSum(ByVal DataRange As Range, ByVal HeaderRange As Range) As String
    Dim Data() As Variant       ' read data into array
    Data = DataRange.Value
    
    Dim Header() As Variant     ' read header into array
    Header = HeaderRange.Value
    
    Dim Result As String        ' collect results for output here
    
    Dim iCol As Long
    For iCol = 1 To UBound(Data, 2)  ' loop through data and concatenate headers
        If Data(1, iCol) = 1 Then
            Result = Result & IIf(Result <> vbNullString, ", ", vbNullString) & Header(1, iCol)
        End If
    Next iCol
    
    ScopeSum = Result  ' output results
End Function

然后在单元格中使用以下公式P3：

=ScopeSum(Q3:Z3,$Q$2:$Z$2)

确保标题用$公式中的符号固定。并将其复制下来：

在此处输入图像描述

这样做的好处是您永远不需要更改代码，即使范围发生了变化。此外，您只需调整公式中的范围即可轻松添加Item 11而不更改代码。

推荐阅读