0
    Dim part As String
    Dim found As Range

newPart:
    Windows("Master Cleaned Kits Oct updated.xlsx").Activate
    part = InputBox("Enter Part No.", "Part Number Lookup")

    Set found = Cells.Find(what:=part, After:=ActiveCell, LookIn:=xlFormulas,LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

    If found Is Nothing Then
        msg2 = MsgBox("Error! Invalid part number.", vbOKOnly)
        GoTo newPart
    End If

    Range(ActiveCell, ActiveCell.End(xlDown).Offset(-1)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy

When I run this macro, I get a run time error 424 on the Find.

I am looking for the macro to return to the newPart: label if the number entered is incorrect or left empty so that the user can try enter the part number again.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Carl O' Beirne
  • 71
  • 7
  • 4
    Remove the `.Activate` in the `Find()` line and read and apply [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Jul 31 '18 at 15:03
  • As @Pᴇʜ said, you need to remove the `.Activate` because when nothing is found, you're trying to activate nothing. Using selected cells in VBA can cause some issues and you should try to avoid it if possible. – DavidP Jul 31 '18 at 15:09
  • 1
    You're also potentially selecting and copying an awful lot of cells with the last three lines, you may want to look at a better way to copy the relevant cells – cybernetic.nomad Jul 31 '18 at 15:16

0 Answers0