Filter a range that the value of my cell number falls into

[Jimmy509]

Hi,
I am working with 2 worksheet in the same workbook. Sheet1 has the values. Sheet 2 has the table of data with header.
My goal is to read the values from sheet1 and then filter a specific column with that said value. I can get that far. The issue is my value in sheet1 is not an exact value of what would be in sheet2.
for example. In sheet1 I have values of a = 5.68. Now when I go in sheet2 it does not have 5.68 in the "a" column but I want to select and filter the values that a =5.68 falls into. for example sheet2 may have 4.5 and 7.5. if those are the values closest/nearest to the a values then I want to filter them. In summary if a = 5.68 in sheet 2, then I go in sheet2 and filter the range that 5.68 falls into and those values would be 4.5 and 7.5 and take the resulting value in the next column and paste it in a new sheet.
Here's what I started with:

Sub Filter_criteria()
Dim A As String

With Worksheets("Sheet1")

Set Al = .Range("A11")

End With

With Worksheets("Sheet2")
With .Range("A1:N" & Cells(.Rows.Count, "N").End(xlUp).Row)
.AutoFilter
Range("K1").AutoFilter Field:=11, Criteria1:=lat, Operator:=xlFilterValues

End With
.AutoFilterMode = False
End With
End Sub

 

[jasonb75]

Do you specifically need to filter, or just find the correct rows?

Given that there are no filter options for closest value, you would need to find out exactly what the closest values are before you could filter them. The code below will find either an exact match or the closest value above and below that specified. For testing, I've used a message box to show the rows where the values are found rather than a filter or copy and paste.

Is it possible that (using your example) either of the values, 4.5 and 7.5 could be in multiple rows? The code only allows for each to appear once, so if this is possible then it will need some changes.

Option Explicit
Sub test()
Dim sVal As Double, lRow As Long, sFound As Range, lRng As Range, uRng As Range, c As Range

sVal = Worksheets("Sheet1").Range("A11").Value
With Worksheets("Sheet2")
        lRow = .Cells(Rows.Count, 14).End(xlUp).Row
    With .Range("K2:K" & lRow)
            Set sFound = .Cells.Find(sVal, , xlValues, xlWhole)
        If Not sFound Is Nothing Then
            MsgBox sFound.Offset(, -10).Resize(, 14).Address(0, 0)
        Else
            For Each c In .Cells
                If c.Value > sVal Then
                    If uRng Is Nothing Then
                        Set uRng = c
                    Else
                        If c.Value < uRng.Value Then Set uRng = c
                    End If
                Else
                    If lRng Is Nothing Then
                        Set lRng = c
                    Else
                        If c.Value > lRng.Value Then Set lRng = c
                    End If
                End If
            Next
                MsgBox lRng.Offset(, -10).Resize(, 14).Address(0, 0) & vbCrLf & uRng.Offset(, -10).Resize(, 14).Address(0, 0)
        End If
    End With
End With
End Sub

 

[Jimmy509]

I have to filter those values in that column... in column A... row 2 (A2) may be 4.5 and row 20 (A20) may be 7.5..but those are the values that 4.5 fall within. Because filtering those values will results in another set of values in column B. Then I also have a b value that I will filter the range for in column. I would do that process 3 times with 3 values (a,b,c) from sheet1 and filter the range they fall into in sheet2 in column (A,B,C) to get a final values in Column D.....

 

[jasonb75]

It should be possible to apply filters using the values of sFound, lRng and uRng as the criteria where I've used the message boxes for testing.
I've just tried it but encountered several errors, will take another look in the morning, probably a simple typo that I'm missing.

 

[jasonb75]

Found the problem, this appears to filter correctly, the variable sVal defines the value on which the filter is based.

Option Explicit
Sub test()
Dim sVal As Double, lRow As Long, sFound As Range, lRng As Range, uRng As Range, c As Range

sVal = Worksheets("Sheet1").Range("A11").Value
With Worksheets("Sheet2")
        lRow = .Cells(Rows.Count, 14).End(xlUp).Row
    With .Range("A1:N" & lRow)
        .AutoFilter
            Set sFound = .Cells.Find(sVal, , xlValues, xlWhole)
        If Not sFound Is Nothing Then
            .AutoFilter Field:=11, Criteria1:=sFound.Value
        Else
            For Each c In .Columns(11).Cells
                If c.Value > sVal Then
                    If uRng Is Nothing Then
                        Set uRng = c
                    Else
                        If c.Value < uRng.Value Then Set uRng = c
                    End If
                Else
                    If lRng Is Nothing Then
                        Set lRng = c
                    Else
                        If c.Value > lRng.Value Then Set lRng = c
                    End If
                End If
            Next
            .AutoFilter Field:=11, Criteria1:=lRng.Value, Operator:=xlOr, Criteria2:=uRng.Value
        End If
    End With
End With
End Sub

 

[Jimmy509]

thanks that work beautiful but I just realized it's not as easy as I though to do the same filter for column 12 and 13. can you help? if I have issues doing that in the if statement

 

[Jimmy509]

thanks that work beautiful but I just realized it's not as easy as I though to do the same filter for column 12 and 13. can you help? if I have issues doing that in the if statement. I would like it to do the same for b in sheet1("A12") and c in sheet1("A13") and match and filter the number in sheet2. I tried to modify the one you made which works great but I created errors

 

[jasonb75]

To make sure that I'm following correctly, A11 is filtering column K, A12 is filtering column B and A13 is filtering column C?

Are these filters all being applied together or individually?

Are they all using the same principle of exact match or closest match above and below?

Also, I'm going to suggest an additional test with a value in A11 that is either less than the minimum value in column K, or greater than the maximum value. Does it work, or create an error?

 

[Jimmy509]

No A11 values filter K, A12 filter L and A13 filter column M. They're using the same principle in the code you provided. Only after filter all 3 columns I want to take the max values in column N and paste it in a new sheet in the same workbook. The filtering can happen together as long as they filter the appropriate columns.
I already tested the code with a value for A11, it works great

 

[jasonb75]

Ok, now I follow. I'm not sure that it will work in the way that you want it to, but I'll try it anyway.
Don't have much free time to work on it this week so it might be a couple of days before I post the code.