Finding the closest value

[Mizzness]

Hello.

Can someone help with the following problem:
I would like to create a macro that compares the value of my target cell to a range of designated cells.

My example is as follows:
Target Cell (F1) = 100
Cell (X1)= 101
Cell (Y1)= 102
Cell (Z1)= 103
Cell (AA1)= 99.5

I would like the macro look up the cells in my range (X1:AA1) with the closest value to my target cell (F1) and paste that value in a seperate blank tab.

Can this be done ?
If I expand my range, will it be easier to have the macro look up the column name instead of a range ?

Thanks for any and all help.

 

[ETID]

sub match_it()

dim best_match as long

best_match=application.worksheetfunction.hlookup(sheets("target_cell_sheet&quot.range("f1&quot,sheets("your_sheet_name_here&quot.range("x1:aa1&quot,1)

seperate_blank_tab=best_match

end sub

 

[Zathras]

Assuming your data is truly unsorted and not a typo, then this UDF can be used to give you what you want:
[blue]

Function ClosestMatchUnsorted(LookupWith As Double, LookupWhere As Range) As Double
Dim c As Range
Dim Closest As Range
Dim Closeness As Double

  Set Closest = LookupWhere.Cells(1, 1)
  Closeness = Abs(LookupWith - Closest.Value)
  For Each c In LookupWhere
    If Abs(LookupWith - c.Value) < Closeness Then
      Set Closest = c
      Closeness = Abs(LookupWith - c.Value)
    End If
  Next c
  ClosestMatchUnsorted = Closest.Value
  Set Closest = Nothing
End Function

[/color]
HLookup requires the data to be sequenced.