Finding Absolute Values from an Array with VBA

[VIGILANCE]

Hi,

array formula =IFERROR(INDEX($A7:$A4031,MATCH(MIN(ABS(B$7:B4031-B4)),ABS(B$7:B4031-B4),0)),"")

In the formula above I need some way to find the ABS part using VBA. Please advise.

 

[Scott Huish]

What are you planning on doing with it exactly? You could add each of the values to a collection.

Sub Test()
Dim col As New Collection
Dim c As Range, t As Long
For Each c In Range("B7:B4031")
    col.Add Abs(c - Range("B4"))
Next
For t = 1 To col.Count
    MsgBox col(t)
Next
End Sub