Type mismatch on cell values after VBA update

[Jhieminga]

Hello all,

I'm hoping to get some help with the following issue. I've written a set of macros around a few worksheets that I analysed for a thesis project. The project is now finished but I'm doing some follow up work and thought it would be safe to update my Office 2016 for Mac now. After this update, I've got the new and improved VBA editor, but I've also got an error on a macro that used to run like clockwork before the update.

The issue is with this bit of code:

For Each Rw In Worksheets("Cats").Rows
            If Worksheets("Cats").Cells(Rw.Row, 1).Value = Worksheets("Full List with Narrative").Cells(Line, 1).Value Then
                If Worksheets("Cats").Cells(Rw.Row, 2).Value = Worksheets("Full List with Narrative").Cells(Line, 2).Value Then
                    If Worksheets("Cats").Cells(Rw.Row, 4).Value = Worksheets("Full List with Narrative").Cells(Line, 4).Value Then
                        .Cells(1, 8).Value = Rw.Row
                        Exit For
                    End If
                End If
            End If
        Next

Both worksheets contain rows relating to the same incidents, but the line numbers aren't the same (there could be up to four rows in the Cats worksheet relating to the same single line in the other worksheet) so I'm having to match the values in three columns to find the correct rows. Column 1 contains a date or is empty, same for column 2 and column four contains a string. I'm now getting a Type Mismatch error when the first If statement runs. I checked the two sides using the Watch window and both are Variant/String types but one is showing 'Error 2029' as the value.

I'm in a bit of a bind as I need this to work for a presentation next week and it's been working fine up until I updated my Office installation.... any help is welcome!

 

[steve the fish]

Look through the appropriate cells on the worksheets in question. Theres probably an error cell in there somewhere.

 

[Jhieminga]

You're right, there was one in there. That is strange as this was somehow never an issue before the update. The original data sheets are 'as received' and I wrote the macros to only read from them, but I have now removed the data from that single cell and all is well with the world again.