Solved

Search for string in several cells

Posted on 2012-04-07
8
444 Views
Last Modified: 2012-04-08
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
0
Comment
Question by:AndreasHermle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 47

Expert Comment

by:Martin Liss
ID: 37819304
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
 
LVL 47

Assisted Solution

by:Martin Liss
Martin Liss earned 125 total points
ID: 37819383
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
 
LVL 10

Accepted Solution

by:
Anthony Berenguel earned 250 total points
ID: 37819421
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst
Ingeborg Hawighorst earned 125 total points
ID: 37820181
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
 
LVL 47

Expert Comment

by:Martin Liss
ID: 37820216
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
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 37820357
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
 

Author Comment

by:AndreasHermle
ID: 37821311
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
 

Author Closing Comment

by:AndreasHermle
ID: 37821313
Very hard to distribute points!

Thank you very much. Regards, Andreas
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question