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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.