Finding the location of closest match in a given list is a task I was given recently. The list was rather extensive and I needed to generate a nearest match and place the term “Close” in the adjacent column. It is a tricker task than first anticipated as you need to keep a running total of the number closest to the target figure. The following VBA code will loop through a range of cells and evaluate the closest match.
SubNearestFig()
Dim rng as Range
Dim r as Range
Dim Mx as Long
Dim i as Long
Const target as Integer = 100
Set rng = Range([B2], Range("B" & Rows.Count).End(xlUp))
rng.Offset(, 1).ClearContents
Mx = Application.Max(rng)
For Each r In rng
If Abs(target - r) < Mx Then
Mx = Abs(target - r)
i = r.Row
End If
Next r
Cells(i, 3) = "Close"
End Sub
The key to finding the closest value is to find the smallest number between your target (the number you are aiming to match) and the number in the cell being evaluated. If the difference between the two values is zero then you have your match, otherwise the closest number to zero in your range is the closest match as this number is the nearest.
For this particular file the numbers being evaluated are in column B. Change to suit. A file is attached below to show workings.
