Find Array

I am wondering whether a VBA can be written which will consider a Range (say. Sheet1(Range("A20:A2000")) as a FIND ARRAY?

With rng2
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                'Application.Goto Rng, True
                Call Find_Exception
            Else
               Call clearComm
            End If
        End With

The above code is only searching a Value .. instead I want to supply entire A20:A2000 values as a Search Range?
With rng2
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                'Application.Goto Rng, True
                Call Find_Exception
            Else
               Call clearComm
            End If
        End With

Open in new window

LVL 9
suvmitraAsked:
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.

Rory ArchibaldCommented:
You would have to loop through each cell in that range repeating the find with each value. You can't do them all in one go with Find, although you might be able to with a formula.
suvmitraAuthor Commented:
Thank you Rorya! My Idea is as below..
SheetA Range A2: A2000 will contain all the values I want to search
Sheet B Range E20 : E is my Search Range.
I need a VBA for this. Please help.
Rory ArchibaldCommented:
Do you want to run Find_Exception for every match that is found, or just once if any match is found?
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

suvmitraAuthor Commented:
I need to highlight all the matches found in Sheet2 E20:E for the List In Sheet1.A2:A2000
suvmitraAuthor Commented:
I am only wondering with the below line of code..what if I have 1000 different search values to be inserted?
 
 FindString = Array("12501", "41001")
Rory ArchibaldCommented:
Not sure that really answers my question, but try:

With rng2
   For Each rngcell in Sheets("Sheet1").range("A2:A2000")
            Set Rng = .Find(What:=rngcell.value, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                'Application.Goto Rng, True
                Call Find_Exception
            Else
               Call clearComm
            End If
    Next rngcell
End With

Open in new window

suvmitraAuthor Commented:
Thank you Rorya! This is perfectly working!
But It is taking too much time as the list I have is containing almost 10000 values!
Is that possible for us to search this as criteria >=40000 And < 50000
This will save the unncessary visit to each cell for the procedure..is that possible Rory?
 
Rory ArchibaldCommented:
That's a totally different question. You wouldn't use Find at all for that - you would loop through the actual data cells and colour them if necessary or, in reality, just use conditional formatting on the sheet to do it.
suvmitraAuthor Commented:
Thanks Rory. Please have a look at the attached file. Please check the code I am trying to use and advice if there is anything wrong. It is not working.
Procedure should look into Sheet2 E20:E range for the values listed in Sheet1 A2:A and if the value/s found then
highlight the other values which are not listed in the range.
I know it is not the same which I have requested .. but I am trying to perform only this. Your help is very much appreciated.

Ex-02.xls
Rory ArchibaldCommented:
Your code is looking at column E but the account numbers are in column C! :)
suvmitraAuthor Commented:
Sorry..but I have now added two more columns but it is still not working :(
Rory ArchibaldCommented:
Having looked at your code more closely, I don't understand it at all. You loop through the range trying to find certain account numbers and if you find one, you then call another sub that loops through the range performing the same checks again. Why?
suvmitraAuthor Commented:
Please correct me if I am wrong,
In my Code I have total 3 loops..
1. Find_12501_2() is for finding matching values in E20:E which are listed in Sheet1
2. If Value find then call clearComm() which will further validate whether the result values are celle >= 41000 Or celle = 12501 and Add comment to the other values and highlight as RED
3. If there is no match found then call clearComm2() which will be clearing all comments and interiors.
===
Now I realized it can be also done in some other way .. I have re-modified the code ..but it is still not working .. please help.

Sub Find_12501_2()
Application.ScreenUpdating = False

    Dim FindString As Variant, FindString2 As String, FindString3 As String, i As Long
    Dim rng As Range, rng2 As Range, rngcell As Range
With ActiveSheet
    Set rng2 = Range(.Cells(20, "E"), .Cells(.Rows.Count, "E").End(xlUp))
End With
    FindString = Array("12501", "41001")
    FindString2 = 41000
    FindString3 = 49999
   

With rng2
   For Each rngcell In ThisWorkbook.Sheets("Sheet1").Range("A2:A9002")
            Set rng = .Find(What:=rngcell.Value, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not rng Is Nothing Then
                'Application.Goto Rng, True
                'Call clearComm
                rngcell.ClearComments
            rngcell.Interior.ColorIndex = 2
            Else
               'Call clearComm2
            rngcell.ClearComments
            rngcell.AddComment "Entry in Revenue Account!"
            rngcell.Interior.ColorIndex = 3
            End If
    Next rngcell
End With
Application.ScreenUpdating = True
End Sub

Open in new window

Rory ArchibaldCommented:
Try this:

Sub Find_12501_2()
    Application.ScreenUpdating = False

    Dim FindString As Variant, FindString2 As String, FindString3 As String, i As Long
    Dim rng As Range, rng2 As Range, rngcell As Range
    
    With ActiveSheet
        Set rng2 = Range(.Cells(20, "E"), .Cells(.Rows.Count, "E").End(xlUp))
    End With
    Set rng = ThisWorkbook.Sheets("Sheet1").Range("A2:A9002")
    
   For Each rngcell In rng2
        If Not IsError(Application.Match(rngcell.Value, rng, 0)) Then
            rngcell.ClearComments
            rngcell.Interior.ColorIndex = 2
        Else
            rngcell.ClearComments
            rngcell.AddComment "Entry in Revenue Account!"
            rngcell.Interior.ColorIndex = 3
        End If
    Next rngcell
    Application.ScreenUpdating = True
End Sub

Open in new window

suvmitraAuthor Commented:
Thank you Rory!
I have tried (as attached) .. but if there is no Account No is found VBA is not clearing the cells?
All the Red (please look at the workbook) should be clear.

Ex-02.xls
Rory ArchibaldCommented:
Then your criteria are the wrong way round! Remove the word 'Not' from line 13 in my code.

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
suvmitraAuthor Commented:
Thank you rory .. it works!
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.