• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

.find not working

mdavid

Member
I have the following code:
<
>>> use code - tags <<<
Code:
Sub find_match()
Dim I, total, fRow As Integer
Dim found As Range

total = Sheets(1).Range("B" & Rows.Count).End(xlUp).Row

For I = 2 To total
    answer1 = Worksheets(1).Range("B" & I).Value
    Set found = Sheets(2).Range("A2:B" & Sheets(2).Cells(Sheets(2).Rows.Count).End(xlUp).Row).Find(what:=answer1) 'finds a match
If found Is Nothing Then
    Worksheets(1).Range("P" & I).Value = "NO MATCH"
    GoTo NextIteration
End If
If IsNumeric(Sheets(2).Cells(found.Row, 1).Value) And IsNumeric(Sheets(2).Cells(found.Row, 2).Value) Then
    Sheets(1).Rows("I:I").Copy
    Sheets("Complaints Common").Paste
Else
    Sheets(1).Rows("I:I").Copy
    Sheets("Complaints not Common").Paste
End If
NextIteration:
Next I
End Sub
/>
I want to look for a number (that's in sheet1 column B) in Sheet(2) in column A or column B.
<
Code:
    Set found = Sheets(2).Range("A2:B" & Sheets(2).Cells(Sheets(2).Rows.Count).End(xlUp).Row).Find(what:=answer1) 'finds a match
/>
Always returns nothing which is incorrect 'cause every row in Sheet1 has a match in sheet 2.
What am I doing wrong?
Thanks for any help
 
Last edited by a moderator:
mdavid
Have You tried to run Your code row-by-row?
... and see/verify ... what would work?
After that, You should find out - how would Your code work?
... for my eyes - it seems that 'something' is missing.
Ps: .Find would work ... if it uses as it should use.

You could also reread Forum Rules:
especially How to get the Best Results at Chandoo.org
 
Thanks Vletm,
I did run with F8 and see that Set found is always Nothing - don't understand why, like to learn if you can point out the error.
Thanks
 
mdavid
You seems to read only my the first question
... I cannot understand why?
Did You reread Forum Rules as I gave a hint?
 
If this helps anybody - this is what worked for me.
<code

As You've noted >>> use code - tags <<<
Code:
Sub find_match()
Dim I, fRow As Integer
Dim commonRow, notCommonRow, totComplaints, totSpecies As Long
Dim found As Range
notCommonRow = 1
commonRow = 1
totComplaints = Sheets(1).Range("B" & Rows.Count).End(xlUp).Row

For I = 2 To totComplaints
    answer1 = Worksheets(1).Range("B" & I).Value
    Set found = Sheets("Species").Range("A:B").Find(what:=answer1) 'finds a match
If found Is Nothing Then
    Worksheets(1).Range("P" & I).Value = "NO MATCH"
    GoTo NextIteration
End If
If IsEmpty(Sheets(2).Range("A" & found.Row).Value) Or IsEmpty(Sheets(2).Range("B" & found.Row).Value) Then
    notCommonRow = notCommonRow + 1
    Sheets(1).Rows(I).EntireRow.Copy Sheets(4).Range("A" & notCommonRow)
Else
    commonRow = commonRow + 1
    Sheets(1).Rows(I).EntireRow.Copy Sheets(3).Range("A" & commonRow)
End If
NextIteration:
Next I
End Sub
/code>
 
Last edited by a moderator:
Hi
any way
see this
Code:
  Set found = Sheets(2).Range("A2:B" & Sheets(2).Cells(Sheets(2).Rows.Count, 1).End(xlUp).Row).Find(what:=answer1) 'finds a matchEnd Sub
 
Back
Top