[RESOLVED] HELP determine if a certain cell is merged

[Rikki_UW]

I have an application that reads data from tables in excel. The tables have entries which usually take up one line, then the next line is blank and merged across the table, so in order to get to the next entry I advance 2 rows.

The problem arises when entries take up two rows. In this case advancing 2 rows brings you to a blank, merged row. What I want to do is after advancing two rows, determine if that row is merged and if it is advance another row. Is there a way to determine if a certain cell address is part of a larger merged cell?

Thanks,
--Rikki

[westconn1]

if not range("C3").mergecells then

[Rikki_UW]

Ah k thanks. Another problem: I'm trying to determine whether "No Activity for this Account" is present in the worksheet so I am using the find method in an if statement as follows:

If Not oWorkSheet.Cells.Find("No Activity for this Account") = Nothing Then

This works fine if it is not present, but if it is it throws the error "Run-time exception thrown : System.InvalidCastException - Operator '=' is not defined for type 'Range' and 'Nothing'." If I change it to this:

If Not oWorkSheet.Cells.Find("No Activity for this Account").Equals(nothing) Then

Then it works if it is present, but if it isn't it gives the error "Referenced object has a value of 'Nothing'."

How Can I get this to work all the time?

Thanks,
--Rikki

[Siddharth Rout]

I do it this way. Please amend as applicable...

Sub Test()
 
'Switch off Error
On Error Resume Next
 
If Not Sheets("Sheet1").Cells.Find(What:="No Activity for this Account", After:=ActiveCell, LookIn _
        :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False) Then
    MsgBox "Found"
Else
    MsgBox "Not Found"
End If
 
'Switch Error back on
On Error GoTo 0
 
End Sub

[Rikki_UW]

I can't switch off errors because the method also contains a try...catch statement, and it's giving me a type mismatch error.

[Siddharth Rout]

You don't have to switch off and on errors as it seems. you can switch it off just before the IF and switch it on after ENDIF. don't use the code that I have given above... that is to show you how I tested it in excel... use your own code between

On Error Resume Next
'your if code
On Error GoTo 0

[Rikki_UW]

I switched it back to my if code. This is what I have:

                On Error Resume Next
                If Not Not oWorkSheet.Cells.Find("No Activity for this Account") = Nothing Then
                    noActivity = oWorkSheet.Cells.Find("No Activity for this Account", nextFind).Row()
                End If
                On Error GoTo 0

But both the error off and error on lines are underline with the error "Method cannot contain both a 'try' statement and an 'On Error' or 'Resume' statement." Same with the try statement which follows a few lines after errors are switched back on.

Also, which version should I be using? "= nothing" or ".equals(nothing)"?

[Siddharth Rout]

Try this, since it is working when the string is found...

If Not oWorkSheet.Cells.Find("No Activity for this Account").Equals(nothing) Then

[Rikki_UW]

Tried that but it gives the error "Referenced object has a value of 'Nothing'." if it is not present.

I'm trying putting them both in separate try catch statements so that one of them will always work.

[Siddharth Rout]

can you upload your workbook?

[Rikki_UW]

That worked, thanks for the help though. If there's a better way to do it though I'm definitely open to suggestions.

--Rikki

[westconn1]

any object can not = nothing
should be
If Not oWorkSheet.Cells.Find("No Activity for this Account") Is Nothing Then

[Rikki_UW]

Ah that works, thanks westconn1.

Sorry koolsid, missed your post. No I can't, too much confidential information.

--Rikki