Solved

Search for string in several cells

Posted on 2012-04-07
8
437 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 46

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 46

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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 46

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem to With line 4 41
Office 2016 Excel Issue 4 26
InStr Function not working properly in macro 3 19
Google Sheets Youth Baseball Round Robin Scheduling 5 6
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 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

24 Experts available now in Live!

Get 1:1 Help Now