Run-Time error 91 (cannot find searched value) How to handle?

[Knockoutpie]

Occasionally the searched value is not found, when that happens. I get an error 91 (because the value doesn't exist)

What's the proper way to handle this without using "ON Error"

'Activate worksheet
Worksheets("Quote").Activate


'Copy/search part Num
    Dim str1 As String
    Dim Cntr As Integer
    Dim Row As Integer
    Cntr = 0
    Row = 2
    
    'Start of loop
    Do While Cntr <= 650 ' should not loop over 650 rows
Cells.Find(What:="PartNum").Offset(Row, 0).Select ' Find "partnum" in row 3, offset and copy value

' Find the return value
      Cntr = Cntr + 1
      str1 = ActiveCell.Value
      Selection.Copy
      Worksheets("Export").Activate
      ActiveCell.Select
      
      ' search for copied value in Export worksheet
      Cells.Find(What:=str1, After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
      Cells.FindNext(After:=ActiveCell).Activate
      
      ' when copied value is found, copy value from 24 columns to the right
      ActiveCell.Offset(0, 24).Range("A1").Select
      Selection.Copy
      Worksheets("Quote").Activate ' return to quote worksheet
      
     
'Find LeadTime and determine paste location of new offset copied value
Cells.Find(What:="Leadtime").Offset(Row, 0).Select
      Selection.PasteSpecial Paste:=xlPasteValues
      Row = Row + 1
     
    Loop

 

[DanteAmor]

Going through your code, I think it could be simplified like this.

Maybe it would be easier if you put an example of what you have on both sheets and a brief explanation of what you need.
But the following macro does what you put in your code.

Sub test_Dam()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim f As Range
  Dim i As Long, col1 As Long, col2 As Long, ini As Long
  
  'set worksheet
  Set sh1 = Sheets("Quote")
  Set sh2 = Sheets("Export")
  
  Set f = sh1.Cells.Find("PartNum", , xlValues, xlPart, , , False)
  If Not f Is Nothing Then
    col1 = f.Column
    ini = f.Row + 2
    Set f = sh1.Cells.Find("Leadtime", , xlValues, xlPart, , , False)
    If Not f Is Nothing Then
      col2 = f.Column
      For i = ini To sh1.Cells(Rows.Count, col1).End(3).Row
        Set f = sh2.Cells.Find(sh1.Cells(i, col1).Value, , xlFormulas, xlPart, xlByRows, xlNext, False)
        If Not f Is Nothing Then
          sh1.Cells(i, col2).Value = f.Offset(0, 24).Value
        End If
      Next
    End If
  End If
End Sub

About the Find method review the following examples:

 

[Knockoutpie]

Going through your code, I think it could be simplified like this.

Maybe it would be easier if you put an example of what you have on both sheets and a brief explanation of what you need.
But the following macro does what you put in your code.

Sub test_Dam()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim f As Range
  Dim i As Long, col1 As Long, col2 As Long, ini As Long
 
  'set worksheet
  Set sh1 = Sheets("Quote")
  Set sh2 = Sheets("Export")
 
  Set f = sh1.Cells.Find("PartNum", , xlValues, xlPart, , , False)
  If Not f Is Nothing Then
    col1 = f.Column
    ini = f.Row + 2
    Set f = sh1.Cells.Find("Leadtime", , xlValues, xlPart, , , False)
    If Not f Is Nothing Then
      col2 = f.Column
      For i = ini To sh1.Cells(Rows.Count, col1).End(3).Row
        Set f = sh2.Cells.Find(sh1.Cells(i, col1).Value, , xlFormulas, xlPart, xlByRows, xlNext, False)
        If Not f Is Nothing Then
          sh1.Cells(i, col2).Value = f.Offset(0, 24).Value
        End If
      Next
    End If
  End If
End Sub

About the Find method review the following examples:

I've attached images of what's on each sheet. We start on sheet "Quote" find "partnum" move down 2 cells and copy the part number, search for the part on "export".
If the part number is not found on "export" sheet, VBA gives error 91. If it is found, it copies the value found 24 columns to the right of the found value, returns to "quote" and finds where to paste the value based on the text found in row 3 "StdLeadTime"

I set this function up to loop for each part number.

 

[DanteAmor]

Did you try the macro that I gave you in post #2?

 

[Knockoutpie]

Did you try the macro that I gave you in post #2?

Oh i see, that works very well. I'm so accustomed to seeing the cell and cursor move, and my fields were already populated so I did not see your solution worked. Thank you very much. It's well written and very easy to understand.