Using Range.find() doesn't work when the text you look for is in merged cells

Hi,

I have some VBA code that loops until it finds the cells with "Total / day :" text in it (since from that line, no more data is available).

In my test, if that cell is B30 and it's a regular cell (not merged with other cell), the code snipet below doesn't go into the IF (the desirable action).

But if I merge the cell B30 with C30 and D30 to make it look better, the code snipet below do go into the IF, which is bad.

If I select the merged cell, the address of the cell is still B30.

Set oRange = Range("B:B").Find(what:="Total / Day :")
If (oRange Is Nothing) Then
    isValid = False
End If

I tried the find() method with different param (lookat=xlPart, searchorder with rows and columns, etc.) with no success.

Any idea what I'm doing wrong?

Actually, the code snippet is used in a validation function that I call before I start the processing. That function make sure everything is there before I start processing the sheet.

What I find "amusing", is that when I loop through all cell in column "B", and check against this exact same text, it will tell me when the cell contains the text, but when I'm using a built-in function, it won't.

Right now, it would be too time consuming unmerge the cells as many people use the templates. I tried using B:E as the range, but it still doesn't find it.

hi nfuids, can you post sample workbook and code that is not working correctly?

Yeah, I will try and do that today or tomorrow!

the code is on a seperate workbook, I'll combine both for the sake of this forum!

Thanks

Just for the record, I couldn't find the time to produce a sample workbook, so I make a workaround...

Since by looping through column B manually and checking if the text is present works, I created a loop for 300 rows that check if the text is present.
Largest worksheet has like 100 rows. So it shouldn't be a problem.

    For ctp = 0 To 300
        If (Not (LCase(timeSheet.Cells(ROW_DEBUT_CODE_PROJET + ctp, 2)) = "total / jour :")) Then
            foundEndLine = False
        Else
            foundEndLine = True
            Exit For
        End If
    Next ctp

    If (Not foundEndLine) Then
        isValid = False
    End If
      
    AreDataValid = isValid

as far as I'm concerned, you can mark this post solved. I can't find how to do this.

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.


I'm here by asking a moderator to mark this thread as SOLVED.

Regards