Excel VBA UDF查找两个赋值和两个范围之间的最大排列

Question

最近发表了一篇类似的帖子，我不得不删除，因为我的问题措辞错误。我正在尝试创建一个Excel VBA UDF，在该UDF中，将指定范围的每一行与指定值进行比较，并导出序列中的最大范围。请注意，range1和range2的长度总是相等的，我只是试图对该范围中的每个唯一行进行分析。例如，由于有4行，下面的日期集将有4个比较，并且最大范围检查结果将是180%，

loop1 = 3
loop2 = 1000
range1  range2  range1 %    range2 %    maxrangecheck
5       882     67%         -12%                79%
6       842     100%        -16%               116%
8       869     167%        -13%               180%
2       859     -33%        -14%               -19%

下面的代码就是我设计的——我知道它不起作用，但希望能给我一个想法，

Function maxrangecheck(loop1 As Double, loop2 as Double, range1 As range, range2 As range)
Dim i As Range
Dim j As Range
Dim checki As Double
Dim checkj As Double
Dim spread As Double
Dim output As Double
For Each i In range1
checki = i.Value - loop1
For Each j In range2
checkj = j.Value - loop2
spread = checki - checkj
If spread >= spread Then
output = spread
Else:
output = output
Next j
Next i
maxrangecheck = output
End Function

感谢您的帮助！

Answer 1

试试这个。它涵盖了我在评论中提出的观点以及维蒂亚塔的观点。按照目前的情况，如果所有检查都是阴性，它将返回零，因此可能需要更改。

Function maxrangecheck(loop1 As Double, loop2 As Double, range1 As Range, range2 As Range)
Dim i As Long
Dim j As Long
Dim checki As Double
Dim checkj As Double
Dim spread As Double
Dim output As Double
For i = 1 To range1.Count
checki = (range1(i).Value - loop1) / loop1
checkj = (range2(i).Value - loop2) / loop2
spread = checki - checkj
If spread > output Then
output = spread
End If
Next i
maxrangecheck = output
End Function
'a quick test
Sub x()
MsgBox Format(maxrangecheck(3, 1000, Range("A2:A5"), Range("B2:B5")), "0.0%")