VBA Code to Search for specific Text and Display a Messagebox with outcome

[fishandtril]

Hello
I am trying to write a macro that will look at data in a specifc worksheet and range and present a message box if the outcome is TRUE
I have data that is deemed "valid" or "invalid" depending on the data.
I want to remove the msb box if the "invalid" is contained in the range and nothing else needs to be done.
Here is my code:

Sub Test()
    Dim FoundRange As Range
    Dim SearchTerm As String
    
    'Look for something
    SearchTerm = "Invalid"
    Set FoundRange = Worksheets("WWC").Range("H:H").Find(What:=SearchTerm)
    
    'If found
    If Not FoundRange Is Nothing Then
        
       MsgBox "'" & SearchTerm & "' not found"
    Else
        MsgBox "'" & SearchTerm & "' found"
    End If
End Sub

 

[rondeondo]

Hi,
You can search through a range by use of a for loop, something like

for each cellRef in foundRange.cells
....
next

Then in the loop put in your data checks with something like

if cellRef.value = SearchTerm Then
  found = true
end if

You may want to then exit the for loop as soon as it's found once to save searching the entire range.
You might want to also limit the range to the last used range of cells in the sheet rather than the entire 1M rows if the whole column