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.


[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 64"]Y[/TD]
[TD="class: xl64, width: 64"]X[/TD]
[TD="class: xl64, width: 64"]Y-X[/TD]
[/TR]
[TR]
[TD="class: xl63"]282000[/TD]
[TD="class: xl63"]295152[/TD]
[TD="class: xl63"]-13152[/TD]
[/TR]
[TR]
[TD="class: xl63"]288000[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]-7152[/TD]
[/TR]
[TR]
[TD="class: xl63"]288000[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]-7152[/TD]
[/TR]
[TR]
[TD="class: xl63"]300000[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]4848[/TD]
[/TR]
[TR]
[TD="class: xl63"]300800[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]5648[/TD]
[/TR]
</tbody>[/TABLE]

 

[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
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]784000[/TD]
[TD="class: xl65, width: 64, align: right"]97014[/TD]
[TD="width: 64, align: right"]100800[/TD]
[/TR]
[TR]
[TD="align: right"]823200[/TD]
[TD="class: xl65, align: right"]254661,8[/TD]
[TD="align: right"]256000[/TD]
[/TR]
[TR]
[TD="align: right"]862400[/TD]
[TD="class: xl65, align: right"]72760,5[/TD]
[TD="align: right"]75200[/TD]
[/TR]
[TR]
[TD="align: right"]901600[/TD]
[TD="class: xl65, align: right"]286440[/TD]
[TD="align: right"]288000[/TD]
[/TR]
[TR]
[TD="align: right"]940800[/TD]
[TD="class: xl65, align: right"]139536[/TD]
[TD="align: right"]144000[/TD]
[/TR]
[TR]
[TD="align: right"]980000[/TD]
[TD="class: xl65, align: right"]138240[/TD]
[TD="align: right"]144000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl65, align: right"]184320[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl65, align: right"]137484[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl65, align: right"]228000[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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
[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64, align: right"]784000[/TD]
[TD="class: xl65, width: 64, align: right"]97014[/TD]
[TD="width: 64, align: right"]100800[/TD]
[/TR]
[TR]
[TD="align: right"]823200[/TD]
[TD="class: xl65, align: right"]254661,8[/TD]
[TD="align: right"]256000[/TD]
[/TR]
[TR]
[TD="align: right"]862400[/TD]
[TD="class: xl65, align: right"]72760,5[/TD]
[TD="align: right"]75200[/TD]
[/TR]
[TR]
[TD="align: right"]901600[/TD]
[TD="class: xl65, align: right"]286440[/TD]
[TD="align: right"]288000[/TD]
[/TR]
[TR]
[TD="align: right"]940800[/TD]
[TD="class: xl65, align: right"]139536[/TD]
[TD="align: right"]144000[/TD]
[/TR]
[TR]
[TD="align: right"]980000[/TD]
[TD="class: xl65, align: right"]138240[/TD]
[TD="align: right"]144000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl65, align: right"]184320[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl65, align: right"]137484[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl65, align: right"]228000[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.