[VBA] Migrated from Excel 2010 to 2016. Type Mismatch error

[NessPJ]

Hello all,

Recently i have migrated a lot of systems from Office 2010 to 2016.
All of my reports are working, but one is persistently giving me a Type mismatch error now.
I debugged the code but i really can't detect what is going wrong.

The exact error is: Run-time error '-2147352571 (800200005)': Type mismatch.

Here's the code with the error:

Dim LastRowActiviteit, LastRowTag As String
LastRowTag = ThisWorkbook.Sheets("Masterdata").Range("R101").End(xlUp).Row




Dim Row As Integer


    With Registratie.Tag1
    For Row = 5 To LastRowTag
    .AddItem Sheets("Masterdata").Range("R" & Row)
    Next Row
    End With
    
    With Registratie.Tag2
    For Row = 5 To LastRowTag
    .AddItem Sheets("Masterdata").Range("T" & Row)
    Next Row
    End With

I underlined the segment of the code that the debugger points to.

 

[jimrward]

Why is lastrowtag defined as string when it is an integer number

 

[NessPJ]

That's just awful 'coding' on my end. I never got any training for this so..

I changed it to an integer. The error message still persists though. Any ideas?

 

[steve the fish]

Dont use the word Row as a variable. What happens then?

 

[NessPJ]

Dont use the word Row as a variable. What happens then?

I changed the code to the following, but the exact same mismatch still happens:

Dim LastRowTagMD1 As Integer
Dim LastRowTagMD2 As Integer

LastRowTagMD1 = ThisWorkbook.Sheets("Masterdata").Range("R101").End(xlUp).Row
LastRowTagMD2 = ThisWorkbook.Sheets("Masterdata").Range("T101").End(xlUp).Row

Dim RowMD1 As Integer
Dim RowMD2 As Integer


    With Registratie.Tag1
    For RowMD1 = 5 To LastRowTagMD1
    .AddItem Sheets("Masterdata").Range("R" & RowMD1)
    Next RowMD1
    End With
    
    With Registratie.Tag2
    For RowMD2 = 5 To LastRowTagMD2
    .AddItem Sheets("Masterdata").Range("T" & RowMD2)
    Next RowMD2
    End With

 

[MARK858]

Try changing Integer to Long

 

[steve the fish]

What is in

Sheets("Masterdata").Range("R" & RowMD1)

when it fails?

 

[NessPJ]

Try changing Integer to Long

This seems to have solved the issue (i think).

I tried reproducing the error by testing the Excel file on 2 machines in various ways. No errors anymore so far!

What is in

Sheets("Masterdata").Range("R" & RowMD1)

when it fails?

Usually it just shows a valid value such as '5'. It seems to be solved now.

If not, i will report back!