Solved

Search for string in several cells

Posted on 2012-04-07
8
430 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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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 45

Assisted Solution

by:Martin Liss
Martin Liss earned 125 total points
Comment Utility
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
Comment Utility
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
 
LVL 50

Assisted Solution

by:teylyn
teylyn earned 125 total points
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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:teylyn
Comment Utility
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
Comment Utility
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
Comment Utility
Very hard to distribute points!

Thank you very much. Regards, Andreas
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now