Search for string in several cells

Dear Experts:

I would like to run a macro that performs the following task.

On the active worksheet, search for the string 'Drop' in cells B3, G3, I3, J3. The string 'Drop' is just part of the cell entry.

The string has to be found in all of these cells, i.e. in total 4 instances of this string have to be found in these specific cells , if not (say only B3, G3 and J3 contain this string),  a msgbox has to say so and the macro is to exit.

Help is much appreciated.

Thank you very much in advance.  

Regards, Andreas
Andreas HermleTeam leaderAsked:
Who is Participating?
 
Anthony BerenguelCommented:
this solution will tell you which cells are missing 'drop'

Sub validateDrops()
    'for andreasHermle
    '04.07.2012
    Dim b3Drop As Boolean, g3Drop As Boolean, i3Drop As Boolean, j3Drop As Boolean
    Dim drop As String, dropErrors As String, feedbackMessage As String
    
    'initialize variables
    b3Drop = False
    g3Drop = False
    i3Drop = False
    j3Drop = False
    drop = "drop"
    dropErrors = ""
    feedbackMessage = "The following cell(s) are missing the string 'drop': "
    
    'validate drop is in each cell; b2, g3, i3, and j3
    If InStr(LCase(Range("b3")), drop) = 0 Then
        dropErrors = "B3"
    End If

    If InStr(LCase(Range("g3")), drop) = 0 Then
        If Len(droperros) > 0 Then
            dropErrors = dropErrors & ", G3"
        Else
            dropErrors = "G3"
        End If
    End If
    
    If InStr(LCase(Range("i3")), drop) = 0 Then
        If Len(droperros) > 0 Then
            dropErrors = dropErrors & ", I3"
        Else
            dropErrors = "I3"
        End If
    End If
    
    If InStr(LCase(Range("j3")), drop) = 0 Then
        If Len(droperros) > 0 Then
            dropErrors = dropErrors & ", J3"
        Else
            dropErrors = "J3"
        End If
    End If
    
    'see if any cells were missing the string 'drop'
    If Len(dropErrors) > 0 Then
        'display message box with list of cells missing string 'drop'
        MsgBox feedbackMessage & dropErrors, vbCritical
    End If
End Sub

Open in new window

0
 
Martin LissOlder than dirtCommented:
Sub FindFour()
If InStr(UCase(Range("B3")), "DROP") And _
   InStr(UCase(Range("G3")), "DROP") And _
   InStr(UCase(Range("I3")), "DROP") And _
   InStr(UCase(Range("J3")), "DROP") Then
    'it's OK
Else
    MsgBox "Not found in all 4"
End If
End Sub
0
 
Martin LissOlder than dirtCommented:
You may realize this but just to make sure, I want to point out that that macro will find any form of 'drop' like 'dRop' or 'DROp' or 'Drop', etcetera in any part of the cell. If you want to find specifically 'Drop' in any part of the cell then change the macro to

Sub FindFour()
'If InStr(UCase(Range("B3")), "DROP") And _
'   InStr(UCase(Range("G3")), "DROP") And _
'   InStr(UCase(Range("I3")), "DROP") And _
'   InStr(UCase(Range("J3")), "DROP") Then
If InStr(Range("B3"), "Drop") And _
   InStr(Range("G3"), "Drop") And _
   InStr(Range("I3"), "Drop") And _
   InStr(Range("J3"), "Drop") Then

    'it's OK
Else
    MsgBox "Not found in all 4"
End If
End Sub
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

just for the exercise, this can also be done without VBA, as a formula, which will perform much faster.

In a cell enter

=IF(ISERROR(AND(SEARCH(A1,B3),SEARCH(A1,G3),SEARCH(A1,I3),SEARCH(A1,J3))),"not present in all four cells","")

Put the search term into cell A1. The above with the Search() function is not case sensitive. If case matters, use Find() instead, like this:

=IF(ISERROR(AND(FIND(A1,B3),FIND(A1,G3),FIND(A1,I3),FIND(A1,J3))),"Not present in all four cells","")

cheers, teylyn
0
 
Martin LissOlder than dirtCommented:
I'm always interested in better ways of doing things but the asker did request a macro. And as for speed I'm sure you're right but for something like this it's most likely a matter of milliseconds.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
When AndreasHermle asks for a macro, that's probably what's needed. In many other questions, though, askers specify that they want a macro, because they are not aware of the functionality that can be achieved otherwise.

If I can see a way to perform a certain task without VBA (which has several other advantages, apart from the speed factor), I'm more than happy to provide it. Maybe somebody will learn something.

cheers, teylyn
0
 
Andreas HermleTeam leaderAuthor Commented:
Dear all,

Time and time again, I am thrilled at the quality of the support one gets when posting a question on this forum. It is unbelievable how many experts are out there and spend their valuable time helping others.

I can learn from all the solutions,  all of them work just great, and although I specifically asked for a macro I am always glad to see that some experts furnish alternative solutions on a formula basis.

Thank you very much for your professional help. It is hard for me to equitably distribute the points but as a matter of fact, AEBEA's code is the most sophisticated one, hence I he will get the most points.

Thank you very much. I really appreciate your professionalism.

Regards, Andreas
0
 
Andreas HermleTeam leaderAuthor Commented:
Very hard to distribute points!

Thank you very much. Regards, Andreas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.