Excel Vba Abnormal Behavior of Cells.Find on Merged Cells

Question

RESOLVED: Error was caused by manipulating find's properties elsewhere

I'm very new to the world of VBA and I had a question about some abnormal behavior of the Range .Find function I've been experiencing. I have some code that I call in a subroutine as below.

For i = 2 To UBound(pullDataPckg)
        For j = 1 To UBound(section)
            Set cell = dataSheet.Cells.Find(What:=section(j))
           'Below returns nothing on call 2, works for call 1
           'Set cell = dataSheet.Cells.Find(What:="Value from section(j) in second call")

            If Not cell Is Nothing Then
              'Do some stuff
            End If
        Next
Next

pullDataPckg and section are two string arrays, dataSheet is a worksheet object and the code is all located in a Sub that I call to pull data values from a worksheet.

The abnormal behavior I'm experiencing is that Find always returns the cell as Nothing on my second call of the sub (The cells searched for are all merged cells that I know are in the sheet and if I unmerge the cells then the find method is able to locate the cell).

However, if I instead use dataSheet.Range("A1:R999").Find(What:=section(j)) then the method successfully finds the cell. So the problem seemed to be with the Cells property when used with merged cells.

I call the sub two times on the same worksheet so I added a dataSheet.Cells.Find(What:="Known Value in section array of Second Sub Call") call and found that the same code when executed in the first Sub call successfully found the merged cell, while when executed in the second Sub call it failed to find the merged cell (If the cell was unmerged it worked for both calls).

I was hoping someone might be able to shed some light as to why the find method fails when called on the Cells property only in the second Sub call and only with merged cells. Since the test value is hardcoded, able to be found using the Range property/unmerging the cell, and I pass the same worksheet to both calls I'm really scratching my head as to how the Find method could fail only when searching for a merged cell in the second call.

EDIT: There are 5 merged cells that each have a width that spans A:R and a height of 2. Just before the loop starts there is a call to a sub the populates the pullDataPckg array by opening a workbook, pulling information from the workbook into the array, and then closing the workbook. When getting information from the new workbook I call Find() 3 times.

Answer 1

Any Find() settings are persistent between calls (just as they are when you use find via the UI).

If your other use of Find changes any parameters other than What you should be sure to explicitly reset those in any subsequent calls.

In general it's always best to explicitly include all arguments to Find which could affect the outcome (such as LookAt, LookIn etc)