Runtime Error 13: Type Mismatch

[pmgibs]

I keep getting a Runtime Error 13: Type Mismatch with the below code

Private Sub UserForm_Initialize()
HideCloseButton Me
ComboBox1.List = Array("Anti-A", "Anti-B", "Anti-D", "Anti-A,B", "Anti-IgG", "6% Albumin", "PeG+QC AB")
ComboBox2.List = Array("Anti-A", "Anti-B", "Anti-D", "Anti-A,B", "Anti-IgG", "6% Albumin", "PeG+QC AB")
ComboBox3.List = Array("Anti-A", "Anti-B", "Anti-D", "Anti-A,B", "Anti-IgG", "6% Albumin", "PeG+QC AB")
ComboBox4.List = Array("Anti-A", "Anti-B", "Anti-D", "Anti-A,B", "Anti-IgG", "6% Albumin", "PeG+QC AB")
ComboBox5.List = Array("A1 Cells", "A2 Cells", "B Cells", "IgG CC", "SC I&II")
ComboBox6.List = Array("A1 Cells", "A2 Cells", "B Cells", "IgG CC", "SC I&II")
ComboBox7.List = Array("A1 Cells", "A2 Cells", "B Cells", "IgG CC", "SC I&II")
ComboBox8.List = Array("A1 Cells", "A2 Cells", "B Cells", "IgG CC", "SC I&II")
With Worksheets("Lot Numbers")
    If .Range("F11").Value = Array("Anti-A", "Anti-B", "Anti-D", "Anti-A,B", "Anti-IgG", "6% Albumin", "PeG+QC AB") Then
        ComboBox1.Value = .Range("F11").Value
    ElseIf .Range("F11").Value = Array("A1 Cells", "A2 Cells", "B Cells", "IgG CC", "SC I&II") Then
        ComboBox5.Value = .Range("F11").Value
    End If
End With

The error occurs with the first IF .Range("F11") line of code. I have tried calling out the range with out the with, Worksheets("Lot Numbers").Range("F11").Value. I have tried changing it to .Range("F11").Text vs Value, but still run into the same error. Here is a screen shoot of my workbook names.

Am I using Worksheets wrong? When I remove that section of code I don't encounter an error.

Thanks for any help!

 

[Micron]

Adding a msgbox line might help to reveal what the range data type is. Not sure if the issue is on the left or right of =.

With Worksheets("Lot Numbers")
    msgbox "Type Name: " &  TypeName(.Range("F11").Value & "   Value: " & .Range("F11").Value

 

[NateSC]

You are trying to test if a single value equals an array so you have a type mismatch (I think that is the right explanation). There are a couple of ways, but a loop appears to be the fastest.

First change your With-End With to:

With Worksheets("Sheet3")
    If IsInArray(.Range("F11").Value, Array("Anti-A", "Anti-B", "Anti-D", "Anti-A,B", "Anti-IgG", "6% Albumin", "PeG+QC AB")) Then
        ComboBox1.Value = .Range("F11").Value
    ElseIf IsInArray(.Range("F11").Value, Array("A1 Cells", "A2 Cells", "B Cells", "IgG CC", "SC I&II")) Then
        ComboBox5.Value = .Range("F11").Value
    End If
End With

Then add a function:

Public Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    Dim i
    For i = LBound(arr) To UBound(arr)
        If arr(i) = stringToBeFound Then
            IsInArray = True
            Exit Function
        End If
    Next i
    IsInArray = False

End Function

I shamelessly stole this loop from another site.