Macro to compare results of multiple subtractions

[normanblu]

I want to write a macro on excel to subtract a number from many different data points and compare the results. For example, 3 of the data points are 3400, 1700, and 850. If the the number is 1200, I want to write a macro that will find 3400 - 1200, 1700 - 1200, and 850 - 1200 and then determine which has the smallest positive difference. A snag is that I want to discard 850- 1200 because the answer is negative, so the correct answer would be 1700 - 1200.

I'm stuck and not even sure how to go about starting, any help would be greatly appreciated.

 

[ndsutherland]

You don't need a macro

Try array formula

= MIN(IF(#Range>=#,#Range)) confirmed with CTRL+SHIFT+ENTER (not just ENTER)

or if you are trying to find the smallest positive difference

= MIN(IF(#Range>=#,#Range-#)) confirmed with CTRL+SHIFT+ENTER (not just ENTER)

If you insist on a macro

Sub testing()


MsgBox Application.Evaluate("=MIN(IF(A1:A10>=B1,A1:A10-B1))")


End Sub

 

[normanblu]

Thank you so much for your suggestion, ndsutherland. The reason I want a macro is because in reality there are about 20 data points and I will be using this on many different spreadsheets.

 

[ndsutherland]

This is a little more robust

Sub testing()
Dim rrng As Range
Dim ws As Worksheet
Dim nums As String
Dim snum As String
Set rrng = Application.InputBox("Select the range of numbers", , , , , , , 8)
Set ws = rrng.Worksheet
nums = rrng.Address
snum = InputBox("Enter the number to subtract")


MsgBox Application.Evaluate("=MIN(IF(" & nums & ">=" & snum & ",IF(ISNUMBER(" & nums & ")," & nums & "-" & snum & ")))")


End Sub

 

[normanblu]

Thank you so much ndsutherland!!