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.

Can't replicate your results (XL2013) using a simple sub and one merged area containing the value being searched for. In between calling your sub the first and second times, are you using Find() for anything else? It would help if you could post exact steps to reproduce what you're seeing (eg - what ranges merged and how many etc etc) — Tim Williams
I was also unable to reproduce this. But a side note for anyone else investigating: Don't add a watch for Cells in the VBE to check if it has any unexpected properties. Apparently, this convinces the VBE that it needs to grab and store the values of every cell in the sheet (or something like that). Just managed to lock up my computer by hitting 100% memory usage... — Mikegrann
The ranges merged have width (A:R) and height (2). In the above mentioned sub I have a call to another sub just before the loop that opens a workbook pulls data from it into the pullDataPckg array and then closes the workbook. When pulling data from the workbook I use Find(). — wallabe
@wallabe - in that case you should be aware that any Find() settings are persistent between calls (just as they are when you use find via the UI). If your other use of Find changed any other 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) — Tim Williams
@Tim Williams Ah, I was not aware Find() had that property. I added all the values and set it to look after Cells("A1") and that seems to have solved the problem. Is there any reason why find was working when called on Range as opposed to when called on Cells? — wallabe

Tim Williams Tim Williams · Accepted Answer · 2016-08-11T17:02:05

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)

Excel Vba Abnormal Behavior of Cells.Find on Merged Cells

1 Answers