Hello,

I have written the following sub which is called from another "main" sub - 

Sub Check_Cals()

    Dim range0 As Range, range1 As Range, range2 As Range, unionrange1 As Range
    
    Workbooks(MyFile & ".xlsm").Activate
    Set range0 = Worksheets("DataSheet").Range(Worksheets("DataSheet").Cells(4, 10), Worksheets("DataSheet").Cells(3 + ShiftDownNumber, 15))
    Set range1 = Worksheets("DataSheet").Range(Worksheets("DataSheet").Cells(4, 2), Worksheets("DataSheet").Cells(3 + ShiftDownNumber, 7))
    Set range2 = Worksheets("DataSheet").Range(Worksheets("DataSheet").Cells(1, 31), Worksheets("DataSheet").Cells(31, 37))
    Set unionrange1 = Union(range0, range1, range2)
    
    Set p = unionrange1.Find("#N/A Requesting Data...", LookIn:=xlValues)

    If p Is Nothing Then
        ' Does some calculations here

        ' Calls another sub
        Call Transfer_To_Main_Sheet
        
        'saves and closes the file Myfile.xlsm        
        Workbooks(MyFile & ".xlsm").Save
        Workbooks(MyFile & ".xlsm").Close

        Exit Sub
    Else
        Set p = Nothing
        Call Check_Cals
    End If
End Sub

As you can see, Check_Cals() keeps on running till the phrase "#N/A Requesting Data..." is not found in the defined range 'unionrange1'. Once that happens, it does some calculations, saves and closes the opened file 'Myfile.xlsm' and exits the sub. However, when I run this loop, I'm getting this error for the line - 

p = unionrange1.Find("#N/A Requesting Data...", LookIn:=xlValues)
error - "Runtime error '-2147417848 (80010108)' Method 'find' of object 'Range' failed".
Could anyone please guide me why this is happening? And suggest how I can remove this bug?

Thanks,
excelworker_1