Type mismatch on cell values after VBA update

Jhieminga

New Member
Joined
Oct 15, 2018
Messages
2
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:
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!
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
Look through the appropriate cells on the worksheets in question. Theres probably an error cell in there somewhere.
 

Jhieminga

New Member
Joined
Oct 15, 2018
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,313
Messages
5,527,952
Members
409,794
Latest member
ajithppajith

This Week's Hot Topics

Top