Find (Ctrl + F) & VBA Find not working

[gravanoc]

I am copying data into a workbook on two separate tabs, then I need to locate a word so I can set the appropriate range. This usually is not a problem, but something isn't working as intended, and I get a Type Mismatch error. I'm using the same template code I always use, so I doubt that's the issue. Also, if I click on the tab with the word in question and do CTRL + F to find it, it will say it's not found. No formats are set, the two boxes for Match Case & Match entire cell contents are not checked. Within is Sheet, Search is by Rows, and Look In is Formulas, but it doesn't work if I set it to Values or to search by Columns. However, if I set it to find within Workbook it will find it, and if I select an area containing the word it will also find it. Only if it is a single cell and set to Sheet will it fail, unless I select the cell with the content in it.

The area is formatted as a table and has autofilter on when copied and pasted over. It was exported to xlsx format from a Microsoft Form. The cells are all general format.

One last detail is that if I stop the code execution, then CTRL + F will work as normal.

Specific code segment:

Set choiceRng(0) = mergeSht(0).Cells.Find(What:="Oral", After:=nameRng(0), LookIn:=xlFormulas, LookAt:= _
                xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Offset(1, 0)

Full code:

Sub BeginMerger(combBook As Workbook, mentorBook As Workbook, menteeBook As Workbook)

    Dim menteeSht As Worksheet, mentorSht As Worksheet
    Dim menteeRng As Range, mentorRng As Range
    Dim mergeSht(0 To 1) As Worksheet, matchSht As Worksheet
    Dim mergeRng(0 To 1) As Range, matchRng As Range
    Dim nameRng(0 To 1) As Range, choiceRng(0 To 5) As Range
    
    combBook.Sheets.Add
    combBook.Sheets.Add
    
    Set menteeSht = menteeBook.Sheets("Sheet1"): Set mentorSht = mentorBook.Sheets("Sheet1")
    Set mergeSht(0) = combBook.Sheets("Sheet1"): Set mergeSht(1) = combBook.Sheets("Sheet2")
    Set matchSht = combBook.Sheets("Sheet3")
    Set menteeRng = menteeSht.UsedRange: Set mentorRng = mentorSht.UsedRange
    Set mergeRng(0) = mergeSht(0).Range("A1"): Set mergeRng(1) = mergeSht(1).Range("A1")
    
    mentorRng.Copy
    mergeRng(0).PasteSpecial
    menteeRng.Copy
    mergeRng(1).PasteSpecial
    Application.CutCopyMode = False
    
    mergeSht(0).Name = "Mentees": mergeSht(1).Name = "Mentors"
    matchSht.Name = "Matching"

    For x = 0 To 1
    
        mergeSht(x).Cells.Replace What:="strongly agree", Replacement:="5", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
        mergeSht(x).Cells.Replace What:="strongly disagree", Replacement:="1", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
        mergeSht(x).Cells.Replace What:="disagree", Replacement:="2", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
        mergeSht(x).Cells.Replace What:="agree", Replacement:="4", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
       mergeSht(x).Cells.Replace What:="neutral", Replacement:="3", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
            
    Next
    
    Set nameRng(0) = mergeSht(0).Range("E2", mergeSht(0).Range("E1048576").End(xlUp))
    Set nameRng(1) = mergeSht(1).Range("E2", mergeSht(1).Range("E1048576").End(xlUp))
    Set mentorRng = matchSht.Range("A2").Resize(nameRng(0).Rows.Count, 1)
    Set menteeRng = matchSht.Range("B1").Resize(1, nameRng(1).Rows.Count)
    
    For x = 0 To nameRng(0).Cells.Count - 1
    
        mentorRng.Cells(x + 1, 1).Value = nameRng(0).Cells(x + 1, 1).Value
    
    Next
    
    For x = 0 To nameRng(1).Cells.Count - 1
    
        menteeRng.Cells(1, x + 1).Value = nameRng(1).Cells(x + 1, 1).Value
    
    Next
    
    mentorRng.Cells(mentorRng.Cells.Count + 1, 1).Value = "Mentors"
    menteeRng.Cells(1, menteeRng.Cells.Count + 1).Value = "Mentees"
    
    Set choiceRng(0) = mergeSht(0).Cells.Find(What:="Oral", After:=nameRng(0), LookIn:=xlFormulas, LookAt:= _
                xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Offset(1, 0)
    Set choiceRng(1) = mergeSht(0).Cells.Find(What:="Organizational Performance", After:=nameRng(0), LookIn:=xlFormulas, LookAt:= _
                xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).End(xlDown)
    Set choiceRng(2) = mergeSht(1).Cells.Find(What:="Oral", After:=nameRng(1), LookIn:=xlFormulas, LookAt:= _
                xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Offset(1, 0)
    Set choiceRng(3) = mergeSht(1).Cells.Find(What:="Organizational Performance", After:=nameRng(1), LookIn:=xlFormulas, LookAt:= _
                xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).End(xlDown)
    Set choiceRng(4) = mergeSht(0).Range(choiceRng(0), choiceRng(1))
    Set choiceRng(5) = mergeSht(1).Range(choiceRng(2), choiceRng(3))
    
    
End Sub

 

[DanteAmor]

You must indicate a cell in the After parameter, and you are putting a range.
And validate if you actually found the data.
Try like this:

    Set choiceRng(0) = mergeSht(0).Cells.Find(What:="Oral", After:=nameRng(0).Cells(1), LookIn:=xlFormulas, _
                       LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    If Not choiceRng(0) Is Nothing Then
      Set choiceRng(0) = choiceRng(0).Offset(1, 0)