在VBA中做了一个比较体积,如果体积相似就显示隔壁单元格的内容

Function VC(a, b As Range) 'VolumeCompare体积比较
Dim arry() As Variant
Dim i, c, d As Integer
Dim kmin As Double
Dim kmindex As Variant
arry = b.Value

c = UBound(arry, 1) '区域的行数
d = UBound(arry, 2) '区域的列数
kmin = 1 ‘初始化最小值

’以下是查找最小值
For i = 1 To c

'Debug.Print arry(i, 1)
If Abs(a / arry(i, 1) - 1) < kmin Then
kmin = Abs(a / arry(i, 1) - 1)
kmindex = arry(i, 2)
End If
Next
‘当最小值在5%以内显示隔壁单元格的内容,否则就显示内容和差异百分比
If kmin < 0.05 Then
VC = kmindex
Else: VC = kmindex & ":" & kmin
End If

End Function

05-14 12:47