Finding next greater value in unsorted column with VBA

[TheWolf]

Hello

I have X and Y coordinates stored in two columns. I want to enter an X and Y coordinate in to my Userform and get the closest, greater X coordinate with the same Y value.
I thought this would be easy, but I can’t figure out how to do this.

A simplified Example:
If I enter Y =3 and X =35 (Row 6) in to the textboxes in the userform, I would like a msgbox with the value 60 (row 12).

Grateful for all ideas.
Thanks in advance

Ulf

 

[Peter_SSs]

I haven't done this with a Userform, but see if you can adapt this.

TheWplf.xlsm
	A	B	C	D	E
1	Y	X		Y	3
2	2	75		X	35
3	3	30
4	1	10
5	5	40
6	3	35
7	2	45
8	3	65
9	5	20
10	1	50
11	2	55
12	3	60
13	3	70
14	5	25
15	3	15
16
Sheet1

Sub Get_Next()
  Dim SL As Object
  Dim a As Variant
  Dim i As Long
  Dim Yval As Double, Xval As Double
  
  Set SL = CreateObject("System.Collections.SortedList")
  a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
  Yval = Range("E1").Value
  Xval = Range("E2").Value
  For i = 1 To UBound(a)
    If a(i, 1) = Yval And a(i, 2) > Xval And Not SL.Containskey(a(i, 2)) Then SL.Add a(i, 2), i
  Next i
  If SL.Count > 0 Then
    MsgBox SL.GetKey(0)
  Else
    MsgBox "No greater value"
  End If
End Sub

 

[TheWolf]

Thank you for taking the time.

I think I will manage to tweak your code a little bit to suit my needs.

Thanks again, for your help!

Ulf

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.