Type mismatch when comparing two strings

Question

I have the following code that is comparing a combobox on a userform(GUI) to a populated cell on sheet2 of my workbook and I am getting a "type mismatch" error. This was all working until another sub shifted some data into the cells being compared on sheet 2.

My issue lies with if Worksheets(sheet2).cells(1,i).value = LCase(GUI.superCB.Value) then

Worksheets(sheet2).cells(1,i).value Now shows up in the watch as a Variant/Integer which made me think that when the data was shifted it changed the "style" of that cell.

Private Sub NextButton_Click() ''' adds check boxes to frame

Dim i As Integer
'Dim superColm As Integer
For i = 5 To 12
    If Worksheets(Sheet2).Cells(1, i).Value = LCase(GUI.superCB.Value) Then 'problem line is right here
        superColm = i
        Exit For
    Else
    End If
Next i

NextButton.Visible = False
superCB.Visible = False

Run.Visible = True
Frame1.Visible = True



    Dim chk As Control
    Dim idx As Integer

    Dim lastrow As Integer
    lastrow = Worksheets(Sheet2).Cells(Rows.Count, superColm).End(xlUp).Row

    For idx = 1 To lastrow - 1
        Set chk = GUI.Frame1.Controls.add("Forms.CheckBox.1", idx, True)
        'set chk = gui.Frame1.Controls.Add(
        chk.Visible = True
        chk.Left = 5
        chk.Top = (idx - 1) * (chk.Height + 2)
        chk.Caption = Cells(idx + 1, superColm) & "   " & idx
    Next
    With Me.Frame1
        .ScrollBars = fmScrollBarsVertical
            If lastrow <= 10 Then
                .ScrollHeight = .InsideHeight * 1.5
            ElseIf lastrow <= 15 Then
                .ScrollHeight = .InsideHeight * 2.25
            ElseIf lastrow <= 20 Then
                .ScrollHeight = .InsideHeight * 3
            ElseIf lastrow <= 25 Then
                .ScrollHeight = .InsideHeight * 3.9
            ElseIf lastrow <= 30 Then
                .ScrollHeight = .InsideHeight * 4.75
            ElseIf lastrow <= 35 Then
                .ScrollHeight = .InsideHeight * 5.35
            Else
                .ScrollHeight = .InsideHeight * 6.25
            End If
        .ScrollWidth = .InsideWidth * 9
    End With
End Sub

If I have sheet 2 as the active sheet Cells(1,i).value will work however, I need to have sheet 2 hidden from the user in the end. With this working it makes me think that the cell style is not the issue.

I have tried going to Excel.Workbooks("Shawn_sch_v1.2.xlsm").worksheets(sheet2).cells(1,i).value and everything down to the base cells() hoping it was missing a sheet reference but nothing has helped.

Answer 1

A String can safely be compared against any other data type in VBA... except Error.

Comparing a Variant/Error against anything will throw a type mismatch error.

This code is implicitly accessing whatever ActiveSheet is:

chk.Caption = Cells(idx + 1, superColm) & "   " & idx

Cells should be qualified with the specific Worksheet object you mean to work with. If the active sheet contains a value that can't be coerced into a String (e.g. #VALUE! or #REF!), that will throw a type mismatch error.

Worksheets(Sheet2).Cells(1, i).Value = ...

Here Sheet2 is an identifier. The Worksheets indexer wants either an integer value, or a string. If Sheet2 is the code name of a worksheet in ThisWorkbook, you don't need to dereference it from Worksheets - just use it:

Sheet2.Cells(1, i).Value = ...

The Worksheet class doesn't have a default property, so Debug.Print Worksheets(Sheet2) throws error 438 object doesn't support this property or method - and a subsequent member call like .Cells(1, i), also throws a type mismatch error. If you don't have a Sheet2 string variable holding a worksheet name, I suspect that's the bug you're having right now... which means everything above is just what's waiting to bite you :)

If Sheet2 is a string variable that contains a valid sheet name, you can use the IsError function to verify whether a Variant is a Variant/Error:

If Not IsError(Sheet2.Cells(1, i).Value) Then
    ' value is safe to compare against a string
Else
    ' comparing the cell value to anything will throw error 13
End If

Lastly, I would advise against using Rows as a global variable, since it's already a global-scope identifier ([_Global].Rows, implicitly referring to ActiveSheet). Now, renaming that variable with Find/Replace is going to be pretty hard to do without breaking your code: Rubberduck's "rename" refactoring could probably help with doing that safely (disclaimer: I manage that OSS VBIDE add-in project).