Do...Until Lopp w/Embedded If.Then Statement

[TheMulMan]

I am creating a macro that you open while in a client file to search for matches on a Do Not Contact (DNC) Listing. I want the macro to do the following:

1) Open and Sort the Do Not Contact Listing
2) Use a Do...Until Loop to move through the data on the DNC List and an embedded If...Then Statment to check for and highlight any matches in the client file.

Below is the code I have so far. Column P of the DNC List contains the data I want to search by first, then Column D. I keep getting a Compile Error: Variable Not Defined for the MatchCase reference in the If...Then Statement.

I changed the [highlight]highlighted[/highlight] code to the following, but I still receive the same error for the ExactMatch reference:

Any help you can provide is greatly appreciated!!!
Jeff

Initial Code:

Sub DNCList()
'Open Do Not Contact List and Sort
ChDir "G:\"
Workbooks.Open Filename:="G:\Do Not Contact List 07-29-2005.xls"

Cells.Select
Selection.Sort Key1:=Range("P2"), Order1:=xlDescending, Key2:=Range("D2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

'Declare IMS Cell Range
Dim rngP As Range
With Worksheets("Do Not Contact List")
Set rngP = .Range(.Range("P2"), .Range("P2").End(xlDown))
End With


ActiveWindow.ActivatePrevious
Do Until rngP Is Nothing
Cells.Select
Selection.Find(What:=rngP, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
If MatchCase = True Then
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Else: MatchCase = False
End If
Loop
End Sub

New Code:

[highlight]ActiveWindow.ActivatePrevious
Do Until rngP Is Nothing
Cells.Select
ExactMatch = Selection.Find(What:=rngP, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
If Not ExactMatch is Nothing Then
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Else
ExactMatch = False
End If
Loop[/highlight]

 

[PHV]

Dim ExactMatch As Range
...
Set ExactMatch = Selection.Find(...:=False)
If Not ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

    lRow = 0
  [red] [b] SomeValue = "skip"  'your search value[/b][/red]
    Set rngP = Cells.Find(What:=SomeValue)
    lRow = rngP.Row
    Do Until rngP.Row < lRow
         If Not rngP Is Nothing Then
             With rngP.Interior
                 .ColorIndex = 6
                 .Pattern = xlSolid
             End With
        End If
        lRow = rngP.Row
        Set rngP = Cells.FindNext(rngP)
     Loop

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[SkipVought]

BTW,

I would avoid Select and Activate as much as possible.

How Can I Make My Code Run Faster? faq707-4105

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[TheMulMan]

PH,
When I run the code you provided, it gets stuck in the loop and highlights everything in the client file.

Skip,
When I run the code you provided, I receive a Compile Error: Variable Not Defined for the lRow = reference.

I greatly appreciate your suggestions. Any thing else you can suggest?

Jeff

 

[PHV]

Replace this:
Do Until rngP Is Nothing
By this:
Do Until ExactMatch Is Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Works for me.

What STRING are you searching for?

Neither rngP nor ExactMatch will return Nothing in the FIND method. FIND wraps. Check Help. That's why I CHANGED to test the ROW.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}