FIND returns previous data

[ChuckDrago]

This code (partial) was written to interrogate a database. Cell "A2" of another tab contains a Part Number to be sought.

Target = Range("A2")
Sheets("Kanban Data").Select
Dim Nrow As Range
Dim N As Integer
On Error Resume Next
With Sheets("Kanban Data")
Set Nrow = .Columns(2).Find(What:=Target, After:=.Cells(3, 2), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If the Find expression (as pulled down from the Edit menu) is empty, then inserting a Part Number in A2 and executing the macro finds the correct record and returns the its correct row number (N, as defined afterwards in the code). Then, changing the Part Number in cell A2 and executing a new search returns the SAME row as the previous search, despite Target being equal to the new sought after value. Where am I wrong?
Thanks.

 

[Sal Paradise]

I may be wrong, but it looks like you're searching for Range("A2") on the Kanban sheet. I believe you want to find Range("A2").Value

 

[ChuckDrago]

Hi Sal,

No, that was not the issue. Target was interpreted as the right value, even without the indication you suggested. The real problem was that I needed to .Activate the find, so that the found cell became active and, as such, a reference point for me to extract the rest of the record contents (via Offsets).

Thanks anyway.
Chuck

 

[VoG]

I'm not sure what's happening but try removing this line

On Error Resume Next

which may be hiding errors.

 

[ChuckDrago]

ADVERTISEMENT

Thanks for the tip, VoG...

However, my problem was solved by adding .Activate at the end of the Find function. That made the cell containing the sought item to be active. Then, I extracted what I needed via Offsets.

 

[RoryA]

You should not need to activate a cell to get a reference to it. If you do, something else is wrong in your code.

 

[ChuckDrago]

ADVERTISEMENT

Rorya, VoG... I am totally confused here... The macro in question starts like this:

Sub SeekMatch()
Dim Part, Desc, Ware, Loc, MB, Buyer, ABC, Cont As String
Dim OHand, Lead, OQty, Usage, CSize, SS As Integer
Dim Cost As Variant
Sheets("Editing").Select
If Range("A2") = "" Then
MsgBox ("Please enter Part Number to be queried..."), vbExclamation
Exit Sub
End If
Target = Range("A2")
Sheets("Kanban Data").Select
Dim Nrow As Range
Dim N As Integer
On Error Resume Next
With Sheets("Kanban Data")
Set Nrow = .Columns(2).Find(What:=Target, After:=.Cells(3, 2), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If I remove the bolded On Error Resume (as suggested by VoG), I get an error 424 Object Required. If I leave that line and I remove the final Activate (unnecessary as Rorya suggested) the macro does not work as intended. Any sensible explanations????
(the funny thing is that leaving the On Error and the Active the macro works as intended!)
Chuck, flabbergasted to say the least...

 

[VoG]

Check that the value is being found:

Set Nrow = .Columns(2).Find(What:=Target, After:=.Cells(3, 2), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Nrow Is Nothing Then
    MsgBox "Not found"
    Exit Sub
End If

 

[RoryA]

Find returns a Range object, Activate does not. So you can use:

Set Nrow = .Columns(2).Find(What:=Target, After:=.Cells(3, 2), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not Nrow Is Nothing Then
   N = Nrow.Row
Else
   Msgbox Target & " not found!"
End If

for example.

 

[ChuckDrago]

Interesting #$%&@ I am finding...

I inserted the trap VoG suggested and found out that the FIND of a legitimate Part Number (it should be found) returns Nothing!...
In regards to the macro working as intended, what I have is precisely what Rory suggested in his latest, the code being:

If Not Nrow Is Nothing Then
MsgBox ("Match no found!...Enter new record"), vbExclamation
Sheets("Editing").Activate
Range("A2") = ""
Exit Sub
Else
N = ActiveCell.Row
Part = ActiveCell.Offset(0, 0)
Desc = ActiveCell.Offset(0, 1)
Ware = ActiveCell.Offset(0, 2)
Loc = ActiveCell.Offset(0, 3) etc. etc.

This appears to be upside down, inside out or whatever corresponds... eppur it woks....