gpt4 book ai didi

vba - 如何获取 WorksheetFunction.SumProduct() 值以匹配 =SUMPRODUCT()?

转载 作者:行者123 更新时间:2023-12-04 20:15:09 30 4
gpt4 key购买 nike

我正在尝试使用 SUMPRODUCT 来确定一个范围内的所有非空白值是否相同。我能够使用 this answer 将其作为 Excel 公式工作。 ,现在我想通过使用 WorksheetFunction.SumProduct() 在 VBA 中获得结果.

我尝试了以下方法:

Public Function onlyOneValue(sheet As Worksheet) As Boolean

Dim sumProduct As Variant
Dim rng As Range
Dim rngAddress As String
Dim sumProductString As String
Dim column As Long

Set rng = sheet.Range(sheet.Cells(5,20), _
sheet.Cells(65,20))
rngAddress = rng.address
sumProductString = "(" & rngAddress & "<>"""")/countif(" & _
rngAddress & "," & rngAddress & "&"""")"
sumProduct = WorksheetFunction.sumProduct(Evaluate(sumProductString))
onlyOneValue = sumProduct = 1
End If

End Function

当我使用所有非空白值都相同的列进行测试时, sumProduct 的值是 1.63934426229508E-02 ,但是当我复制 sumProductString变成 =SUMPRODUCT()工作表上的公式,我得到 1 的期望值.

如果我不使用 Evaluate()而是使用:
WorksheetFunction.sumProduct(sumProductString)

然后我得到这个错误:
Unable to get the SumProduct property of the WorksheetFunction class

如何在 VBA 中实现此功能?

最佳答案

当我添加 SUMPRODUCT 时,它对我有用进入Evaluate并调整(即:

Public Function onlyOneValue(sheet As Worksheet) As Boolean

Dim rng As Range
Dim rngAddress As String
Dim sumProductString As String

Set rng = sheet.Range(sheet.Cells(5, 20), _
sheet.Cells(65, 20))
rngAddress = rng.Address
sumProductString = "=SUMPRODUCT((" & rngAddress & "<>"""")/countif(" & _
rngAddress & "," & rngAddress & "&""""))"

onlyOneValue = (CLng(Evaluate(sumProductString)) = 1)

End Function

关于vba - 如何获取 WorksheetFunction.SumProduct() 值以匹配 =SUMPRODUCT()?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/28668447/

30 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com