VBA to search range and return value

[nicolehalliday]

Hello,

Thank you in advance for help with this problem!

I have spent quite a bit of time searching the forums for an answer and I've come up with this so far:

Sub vba()

Dim rng As Range
Application.ScreenUpdating = False

For Each rng In Range("F3:DG3")
    If rng.Value > Cells("A3").Value Then
        Cells(3, 3).Select
        Exit Sub
    End If
Next rng
Application.ScreenUpdating = True
End Sub

So basically I want my macro to search the range F3:DG3 and check if there is any value in the cell range that exceeds the value in cell A3. If there is a larger value in the range, I'd like it to enter "YES" in cell C3 (I was just using select to see if it would even work, which it doesn't). I've tried this a number of ways (vlookup, countif, etc.) but I can't get any of them to work when it's comparing a range to a cell, rather than a cell to a cell. I am quite inexperienced at VBA and excel so if anyone has a simple solution to this I would be extremely grateful!!!

 

[John Davis]

Hello,

Thank you in advance for help with this problem!

I have spent quite a bit of time searching the forums for an answer and I've come up with this so far:

Sub vba()

Dim rng As Range
Application.ScreenUpdating = False

For Each rng In Range("F3:DG3")
    If rng.Value > Cells("A3").Value Then
        Cells(3, 3).Select
        Exit Sub
    End If
Next rng
Application.ScreenUpdating = True
End Sub

So basically I want my macro to search the range F3:DG3 and check if there is any value in the cell range that exceeds the value in cell A3. If there is a larger value in the range, I'd like it to enter "YES" in cell C3 (I was just using select to see if it would even work, which it doesn't). I've tried this a number of ways (vlookup, countif, etc.) but I can't get any of them to work when it's comparing a range to a cell, rather than a cell to a cell. I am quite inexperienced at VBA and excel so if anyone has a simple solution to this I would be extremely grateful!!!

From what I see your code works (except I made one change). But be aware that once it finds the first instance where the value in the range exceeds the value in A3 it will exit sub? Perhaps you want it too something else like maybe highlight the cells?

Dim rng As Range
Application.ScreenUpdating = False

For Each rng In Range("F3:DG3")
    If rng.Value > Range("A3").Value Then
        Cells(3, 3).Value = "yes"
        Exit Sub
    End If
Next rng
Application.ScreenUpdating = True