Find function in VBA

[Rsull]

I am currently trying to use the find function to search a column and stop at anything greater than a certain value. I haven't had any luck using the find function for anything but the equals operator. Is there a way to use it or something similar with greater than operator?

 

[Marc L]

​

A filter ?​

 

[Rsull]

I want to search through a column and find the first cell that has a value greater than something, using macros. I don't think a filter would be a good solution.

​

A filter ?​

 

[Joe4]

Welcome to the board!

I agree. I don't think a filter is particularly helpful for what you want to do.

Try this code (just change the column reference and value you want to look for):

Sub MyFindMacro()

    Dim col As String
    Dim lr As Long
    Dim r As Long
    Dim val As Double
    Dim fr As Long
    
'***Indicate which column to look in
    col = "A"
    
'***Indicate value to look for
    val = 7
    
'   Find last row with data in desired column
    lr = Cells(Rows.Count, col).End(xlUp).Row
    
'   Loop through all rows (starting on row 2) and search for value
    For r = 2 To lr
'       See if value in cell is greater than or equal to search value
        If Cells(r, col) >= val Then
            fr = r
            Exit For
        End If
    Next r
    
'   See if value found
    If fr > 0 Then
        Cells(fr, col).Activate
        MsgBox "Value found in cell " & Cells(fr, col).Address
    Else
        MsgBox "No value found"
    End If
    
End Sub

 

[Rsull]

Welcome to the board!

I agree. I don't think a filter is particularly helpful for what you want to do.

Try this code (just change the column reference and value you want to look for):

Sub MyFindMacro()

    Dim col As String
    Dim lr As Long
    Dim r As Long
    Dim val As Double
    Dim fr As Long
   
'***Indicate which column to look in
    col = "A"
   
'***Indicate value to look for
    val = 7
   
'   Find last row with data in desired column
    lr = Cells(Rows.Count, col).End(xlUp).Row
   
'   Loop through all rows (starting on row 2) and search for value
    For r = 2 To lr
'       See if value in cell is greater than or equal to search value
        If Cells(r, col) >= val Then
            fr = r
            Exit For
        End If
    Next r
   
'   See if value found
    If fr > 0 Then
        Cells(fr, col).Activate
        MsgBox "Value found in cell " & Cells(fr, col).Address
    Else
        MsgBox "No value found"
    End If
   
End Sub

Thanks a lot! I used your code and it worked very well!

 

[Joe4]

You are welcome.
Glad it works for you!