Find the first positive number after deducting from a range of numbers VBA

[Sotos13]

Hi all
I have to find a way to solve this problem. I have a column with different numbers for example x1 x2 x3..x1000, and a specific range of 125 y1 y2 y3.. y125 numbers.
i want to find which x number is closest to a y number. So i was thinking to do y1-x1= and y2-x1= and y3- x1= and so on. the smallest positive number will be my correct answer. So in the below example the answer is 4848 and i want vba to bring me back in a specific column the Y number which in this case is 300000. Feel free to ask anything...waiting for some suggestions.

Y	X	Y-X
282000	295152	-13152
288000		-7152
288000		-7152
300000		4848
300800		5648

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Sotos13]

Nice thinking...thanks

 

[Sotos13]

Great Dante!!! It works with one exception though..it reaches up to the point where you have numbers in column A. If you have more numbers in Column B it doesn't calculate them
see below

784000	97014	100800
823200	254661,8	256000
862400	72760,5	75200
901600	286440	288000
940800	139536	144000
980000	138240	144000
	184320
	137484
	228000

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[DanteAmor]

Great Dante!!! It works with one exception though..it reaches up to the point where you have numbers in column A. If you have more numbers in Column B it doesn't calculate them
see below

784000	97014	100800
823200	254661,8	256000
862400	72760,5	75200
901600	286440	288000
940800	139536	144000
980000	138240	144000
	184320
	137484
	228000

<tbody>
</tbody>

They are small details that I do not know, butTry this:

Sub FormulaXY_2()
    Dim lr As Long, lb As Long, i As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    lb = Range("B" & Rows.Count).End(xlUp).Row
    For i = 2 To lb
        With Range("C" & i)
            .FormulaArray = "=INDEX($A$1:$A$" & lr & ",MIN(IF($A$2:$A$" & lr & "-B" & i & ">0,ROW($A$2:$A$" & lr & "))))"
            .Value = .Value
        End With
    Next
End Sub

 

[Sotos13]

Thanks Dante..Great!!!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.