vba to select cell with value equal to or less than textbox entry

[ThomasOES]

I have a column of data with a range named "SortCol". The data is sorted in ascending order. I can use Find to select a cell with a value I enter into a textbox. However, if the range("SortCol") doesn't have the value from the textbox then an error occurs. What code to select the nearest lower value cell if no value matches the textbox entry?

 

[Akuini]

I have a column of data with a range named "SortCol". The data is sorted in ascending order. I can use Find to select a cell with a value I enter into a textbox. However, if the range("SortCol") doesn't have the value from the textbox then an error occurs. What code to select the nearest lower value cell if no value matches the textbox entry?

You can use Match Function.

Take a look:
https://docs.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.match

If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

 

[ThomasOES]

Thanks for the tip Akuini

I've tried many combinations of Match Functions but not having any luck.
I need to select the cell in the Range("SortCol") that is equal or less than the TextBox2 entry

My latest attempt

Dim sortLoc As Range
Set sortLoc = WorksheetFunction.Match(Search_Form.TextBox2.Value, Range("SortCol"), 1)
Application.Goto sortLoc

Any help would be great

Tom

 

[Akuini]

Thanks for the tip Akuini

I've tried many combinations of Match Functions but not having any luck.
I need to select the cell in the Range("SortCol") that is equal or less than the TextBox2 entry

My latest attempt

Dim sortLoc As Range
Set sortLoc = WorksheetFunction.Match(Search_Form.TextBox2.Value, Range("SortCol"), 1)
Application.Goto sortLoc

Any help would be great

Tom

Match funtion returns the relative position of an item in an array that matches a specified value in a specified order.
So you need a long variable to get the result first then use that variable get the range, so:

Dim sortLoc As Range, n As Long
    n = WorksheetFunction.Match(Search_Form.TextBox2.Value, Range("SortCol"), 1)
    Set sortLoc = Range("SortCol").Cells(n)
    Application.Goto sortLoc

 

[ThomasOES]

Thanks again Akuini. The code works if I replace Search_Form.TextBox2.Value with a typed in value.

Like this

Dim sortLoc As Range, n As Long
n = WorksheetFunction.Match(0.05, Range("SortCol"), 1)
Set sortLoc = Range("SortCol").Cells(n)
Application.Goto sortLoc

Cant figure why the textbox value isn't read by code

Tom

 

[Akuini]

Hm, maybe you need to convert the textbox value to double, like this:

n = WorksheetFunction.Match(CDbl(Search_Form.TextBox2.Value), Range("SortCol"), 1)

 

[ThomasOES]

Yes. Magic. o/\o

Thanks Akuini

 

[Akuini]

Yes. Magic. o/\o

Thanks Akuini

Your welcome & thanks for replying