CODEWITHSUNDEEP
vbaexcel
user2395969
user2395969

Reputation: 151

VBA Returns Error after Range.Find finds nothing

I have two worksheets in Excel, New Sheet and Old Sheet. I am trying to search in Column A of Old Sheet if the column contains each of the entries of Column A of New Sheet. I am using the following VBA code to search, but it returns an error on the second search (the non column header search). I have no idea what I'm doing wrong - any help is appreciated. Here is my code:

Sub Sample()

Dim lastRow As Integer
Dim i As Integer
Dim rng As Range
Dim searchrng As Range
Dim searchval As String

lastRow = Sheets("New One").Range("A65000").End(xlUp).Row

Sheets("Old One").Activate
Set searchrng = Sheets("Old One").Range("A1:A10000")


For i = 1 To lastRow

    Sheets("New One").Activate
    searchval = Sheets("New One").Cells(i, 1).Value

    Set rng = searchrng.Find(searchval)

    If Not rng Is Nothing Then
        MsgBox "Found " & searchval & " in " & rng.Address
    Else
        Sheets("New One").Activate
        Sheets("New One").Cells(i, 1).EntireRow.Interior.Color = vbRed
    End If

Next i

End Sub

The error is always Run-time error '1004' - Method 'Find' of object 'Range' failed.

Upvotes: 0

Views: 1703

Answers (2)

user2395969
user2395969

Reputation: 151

Thanks to everyone for the help.

I was able to get it to work by conditional formatting, thanks to Scott Holtzman for the idea. In the end, I used COUNTIF however, instead of IsError.

=COUNTIF('Old One'!$A:$A, 'New One'!$A1)=1

applied to Column A in 'New One' Worksheet.

Upvotes: 0

BruceWayne
BruceWayne

Reputation: 23283

Avoid using .Select

Sub Sample()

Dim lastRow As Integer
Dim i As Integer
Dim rng As Range
Dim searchrng As Range
Dim searchval As String
Dim oldWS As Worksheet, newWS As Worksheet
Set oldWS = Worksheets("Old One")
Set newWS = Worksheets("New One")

lastRow = newWS.Range("A65000").End(xlUp).Row

Set searchrng = oldWS.Range("A1:A10000")


For i = 1 To lastRow

    searchval = newWS.Cells(i, 1).Value

    Set rng = searchrng.Find(searchval)

    If Not rng Is Nothing Then
        MsgBox "Found " & searchval & " in " & rng.Address
    Else
        newWS.Cells(i, 1).EntireRow.Interior.Color = vbRed
    End If

Next i

End Sub

Does that work for you? I tested it in mine and it worked. Make sure the ranges you give are correct.

But, I agree with @ScottHoltzman - you can do this with Conditional Formatting, avoiding the use of VBA.

Upvotes: 1

Related Questions