Dreaded "Type Mismatch" - Cells.Find Array

[mkoenecke]

Hi guys,

Long time reader, first time I've registered. I was wondering if anyone can be so kind as to help me pick the obvious error I'm missing trying to set up an automated workbook query.

It's set up as a userform that will have a button/.LostFocus event to prompt a query checking all sheets for the user's assets.

Currently the click is giving an error at the line "For Each X In GetSearchArray(myValue)"

Here's my code, apologies, I know it needs a tidy.

Private Sub ComboType_Change()    
    If ComboType.Text = "Phone" Then
        LabelIdentify.Caption = "IMEI:"
    Else
        LabelIdentify.Caption = "Asset:"
    End If
End Sub


Sub Check_Click()
    myValue = TextName.Value
[I]    For Each X In GetSearchArray(myValue)[/I]
        If InStr(X, "chris") Or InStr(X, "DET") Then
        Else
            Debug.Print X
            List = List & X & vbNewLine
        End If
    Next
End Sub


Private Sub CommandButton1_Click()
    SelectFirstBlankCell (1) 'Input Column number
End Sub


Private Sub UserForm_Initialize()
    ComboType.AddItem "Phone"
    ComboType.AddItem "Laptop"
    ComboType.AddItem "Desktop"
End Sub


Public Sub SelectFirstBlankCell(SourceCol)
    Dim rowCount As Integer, currentRow As Integer
    Dim currentRowValue As String
    
    If ComboType.Text = "Phone" Then
        Sheet = "Phone"
    ElseIf ComboType.Text = "Laptop" Then
        Sheet = "Laptop"
    ElseIf ComboType.Text = "Desktop" Then
        Sheet = "Desktop"
    Else
        Exit Sub
    End If


    'SourceCol = 6   'column F has a value of 6
    Worksheets(Sheet).Activate
    rowCount = Cells(Rows.Count, SourceCol).End(xlUp).Row + 1
    
    'for every row, find the first blank cell and select it
    For currentRow = 1 To rowCount
        currentRowValue = Cells(currentRow, SourceCol).Value
        If IsEmpty(currentRowValue) Or currentRowValue = "" Then
            Cells(currentRow, SourceCol).Select
            EmptyRow = ActiveCell.Row
            EmptyColumn = ActiveCell.Column
            ProcessInformation (EmptyRow)
            Exit For
        End If
    Next
End Sub


Private Sub ProcessInformation(Row As Integer)
    Dim Column As Integer
    Dim Identifier As String
    'Identifier = TextIdentifier.Text
    Column = 1
    
    Cells(Row, Column).Value = TextName.Text
    Cells(Row, Column + 1).Value = ComboType.Text
    
    Cells(Row, Column + 2).NumberFormat = "@"
    Cells(Row, Column + 2).Value = TextIdentifier.Value
    
    Unload Me
End Sub



Function GetSearchArray(strSearch)
    Dim strResults As String
    Dim DeviceModel As String
    Dim SHT As Worksheet
    Dim rFND As Range
    Dim sFirstAddress
    For Each SHT In ThisWorkbook.Worksheets
        Set rFND = Nothing
        With SHT.UsedRange
            Set rFND = .Cells.Find(What:=strSearch, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlRows, SearchDirection:=xlNext, MatchCase:=False)
            If Not rFND Is Nothing Then
                sFirstAddress = rFND.Address
                Do
                    If DeviceModel = vbNullString Then
                        If SHT.Name = "Mobiles" Then
                            DeviceModel = ThisWorkbook.Sheets("Mobiles").Cells(rFND.Row, rFND.Column + 2)
                        ElseIf SHT.Name = "Laptops" Then
                            DeviceModel = ThisWorkbook.Sheets("Laptops").Cells(rFND.Row, rFND.Column + 3) & " " & ThisWorkbook.Sheets("Laptops").Cells(rFND.Row, rFND.Column + 4)
                        ElseIf SHT.Name = "4G Services" Then
                            DeviceModel = ThisWorkbook.Sheets("4G Services").Cells(rFND.Row, rFND.Column + 5)
                        End If
                    End If
                            If strResults = vbNullString Then
                                Address = " [" & rFND.Address & "]"
                                strResults = SHT.Name & " - "
                                strResults = strResults & DeviceModel & Address
                                DeviceModel = vbNullString
                            Else
                                Address = " [" & rFND.Address & "]"
                                strResults = strResults & "|" & SHT.Name & " - "
                                strResults = strResults & DeviceModel & Address
                                DeviceModel = vbNullString
                            End If
                    Set rFND = .FindNext(rFND)
                Loop While Not rFND Is Nothing And rFND.Address <> sFirstAddress
            End If
        End With
    Next
    If strResults = vbNullString Then
        GetSearchArray = Null
    ElseIf InStr(1, strResults, "|", 1) = 0 Then
        GetSearchArray = Array(strResults)
    Else
        GetSearchArray = Split(strResults, "|")
    End If
    'MsgBox GetSearchArray
End Function

 

[StephenCrump]

Welcome to the Forum!

Given that GetSearchArray may return Null, you should test for this before trying:

For Each X In GetSearchArray(myValue)

 

[mkoenecke]

Welcome to the Forum!

Given that GetSearchArray may return Null, you should test for this before trying:

For Each X In GetSearchArray(myValue)

Hi Stephen!

Thank you so much.

The macro/function works as a standalone, however when I try to run it from the event (button click) it gives the Type Mismatch error.
Ideally I'd like the button to pull the .Value of the text box that is adjacent (TextName.Value), then plug that into the query using the variable "myValue".

Below is a working copy of the Macro - as a standalone (there are some minor changes, however this does not impact it's original function and the error unfortunately does persist). The trick for me is getting it to work on a click event.

Sub Find()
    'myValue = TextName.Text
    myValue = InputBox("Input Name")
    'MsgBox myValue
    
    For Each X In GetSearchArray(myValue)
        If InStr(X, "chris") Then
            Name = Split(X, "-")
            FullName = Name(1)
            FullName = Right(FullName, Len(FullName) - 1)
        End If
        If InStr(X, "DET") Or InStr(X, "chris") Then
        Else
            Debug.Print X
            List2 = List2 & X & vbNewLine
        End If
    Next
    List = "For '" & myValue & "' (" & FullName & ") I found:" & vbNewLine & List2
    MsgBox List
    List = ""
End Sub




Function GetSearchArray(strSearch)
    Dim strResults As String
    Dim DeviceModel As String
    Dim SHT As Worksheet
    Dim rFND As Range
    Dim sFirstAddress
    For Each SHT In ThisWorkbook.Worksheets
        Set rFND = Nothing
        With SHT.UsedRange
            Set rFND = .Cells.Find(What:=strSearch, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlRows, SearchDirection:=xlNext, MatchCase:=False)
            If Not rFND Is Nothing Then
                sFirstAddress = rFND.Address
                Do
                    If DeviceModel = vbNullString And InStr(strSearch, 0) Then
                        If SHT.Name = "Mobiles" Then
                            DeviceModel = ThisWorkbook.Sheets("Mobiles").Cells(rFND.Row, rFND.Column + 3)
                        ElseIf SHT.Name = "Laptops" Then
                            DeviceModel = ThisWorkbook.Sheets("Laptops").Cells(rFND.Row, rFND.Column + 2) & " " & ThisWorkbook.Sheets("Laptops").Cells(rFND.Row, rFND.Column + 3)
                        ElseIf SHT.Name = "4G Services" Then
                            DeviceModel = ThisWorkbook.Sheets("4G Services").Cells(rFND.Row, rFND.Column + 4)
                        ElseIf SHT.Name = "chris" Then
                            DeviceModel = ThisWorkbook.Sheets("chris").Cells(rFND.Row, rFND.Column - 1)
                        End If
                    Else
                        If SHT.Name = "Mobiles" Then
                            DeviceModel = ThisWorkbook.Sheets("Mobiles").Cells(rFND.Row, rFND.Column + 2)
                        ElseIf SHT.Name = "Laptops" Then
                            DeviceModel = ThisWorkbook.Sheets("Laptops").Cells(rFND.Row, rFND.Column + 3) & " " & ThisWorkbook.Sheets("Laptops").Cells(rFND.Row, rFND.Column + 4)
                        ElseIf SHT.Name = "4G Services" Then
                            DeviceModel = ThisWorkbook.Sheets("4G Services").Cells(rFND.Row, rFND.Column + 5)
                        End If
                    End If
                            If strResults = vbNullString Then
                                Sheet = SHT.Name
                                If SHT.Name = "chris" Then
                                    Address = ""
                                Else
                                    Address = " [" & rFND.Address & "]"
                                End If
                                Address = " [" & rFND.Address & "]"
                                strResults = Sheet & " - "
                                strResults = strResults & DeviceModel & Address
                                DeviceModel = vbNullString
                            Else
                                Sheet = SHT.Name
                                If SHT.Name = "chris" Then
                                    Address = ""
                                Else
                                    Address = " [" & rFND.Address & "]"
                                End If
                                strResults = strResults & "|" & Sheet & " - "
                                strResults = strResults & DeviceModel & Address
                                DeviceModel = vbNullString
                            End If
                    Set rFND = .FindNext(rFND)
                Loop While Not rFND Is Nothing And rFND.Address <> sFirstAddress
            End If
        End With
    Next
    If strResults = vbNullString Then
        GetSearchArray = Null
    ElseIf InStr(1, strResults, "|", 1) = 0 Then
        GetSearchArray = Array(strResults)
    Else
        GetSearchArray = Split(strResults, "|")
    End If
    'MsgBox GetSearchArray
End Function

 

[StephenCrump]

My comment still stands. Perhaps you could do something like this:

vResult = GetSearchArray(myValue)


If vResult = Null Then
    'Do something?
Else
    For Each X In vResult
    ....
    Next X
End If

 

[mkoenecke]

My comment still stands. Perhaps you could do something like this:

vResult = GetSearchArray(myValue)


If vResult = Null Then
    'Do something?
Else
    For Each X In vResult
    ....
    Next X
End If

Ah I see! Apologies, I misunderstood.

I've implemented the below;

Sub Check_Click()
    myValue = TextName.Value
    vResult = GetSearchArray(myValue)
[I][B]    If vResult = Null Then[/B][/I]
        MsgBox "Test error"
    Else
        For Each X In GetSearchArray(myValue)
            If InStr(X, "chris") Or InStr(X, "DET") Then
            Else
                Debug.Print X
                List = List & X & vbNewLine
            End If
        Next
    End If
End Sub

The same error (Type Mismatch) now moves to line "If vResult = Null Then"

 

[StephenCrump]

Oops, sorry ....

If VarType(vResult) = vbNull Then

 

[mkoenecke]

Oops, sorry ....

If VarType(vResult) = vbNull Then

Nailed it! Thank you so much.

Full code below for those who wish to see (Still a mess)

Private Sub ComboType_Change()
    If ComboType.Text = "Phone" Then
        LabelIdentify.Caption = "IMEI:"
        For i = ComboMake.ListCount - 1 To 0 Step -1
            ComboMake.RemoveItem i
        Next i
        ComboMake.AddItem "iPhone"
        ComboMake.ListIndex = 0
    ElseIf ComboType.Text = "Laptop" Then
        LabelIdentify.Caption = "Asset:"
        For i = ComboMake.ListCount - 1 To 0 Step -1
            ComboMake.RemoveItem i
        Next i
        ComboMake.AddItem "Dell"
        ComboMake.AddItem "HP"
        ComboMake.AddItem "Toshiba"
    Else
        LabelIdentify.Caption = "Asset:"
    End If
End Sub


Sub Check_Click()
    Dim List As String
    Dim list2 As String
    Dim FullName As String
    myValue = TextName.Value
    'myValue = InputBox("Input Name or Staff Number")
    'MsgBox myValue
    
    For Each X In GetSearchArray(myValue)
        If InStr(X, "chris") Then
            'Name = Split(X, "-")
            'FullName = (1)
            'FullName = Right(FullName, Len(FullName) - 1)
        End If
        If InStr(X, "DET") Or InStr(X, "chris") Then
        Else
            Debug.Print X
            list2 = list2 & X & vbNewLine
        End If
    Next
    List = "For '" & myValue & "' (" & FullName & ") I found:" & vbNewLine & list2
    MsgBox List
    'With New MSForms.DataObject
        '.SetText List
        '.PutInClipboard
    'End With
    List = ""
End Sub


Private Sub CommandButton1_Click()
    SelectFirstBlankCell (1) 'Input Column number
End Sub


Private Sub ManualMake_Click()
    If ManualMake.Value = "True" Then
        ComboMake.Visible = False
        TextMake.Visible = True
    Else
        ComboMake.Visible = True
        TextMake.Visible = False
    End If
End Sub


Private Sub UserForm_Initialize()
    'Call RemoveCaption(Me)
    ComboType.AddItem "Phone"
    ComboType.AddItem "Laptop"
    ComboType.AddItem "Desktop"
    ComboMake.AddItem "Please select a Type first"
End Sub


Public Sub SelectFirstBlankCell(SourceCol)
    Dim rowCount As Integer, currentRow As Integer
    Dim currentRowValue As String
    
    If ComboType.Text = "Phone" Then
        Sheet = "Phone"
    ElseIf ComboType.Text = "Laptop" Then
        Sheet = "Laptop"
    ElseIf ComboType.Text = "Desktop" Then
        Sheet = "Desktop"
    Else
        Exit Sub
    End If


    'SourceCol = 6   'column F has a value of 6
    Worksheets(Sheet).Activate
    rowCount = Cells(Rows.Count, SourceCol).End(xlUp).Row + 1
    
    'for every row, find the first blank cell and select it
    For currentRow = 1 To rowCount
        currentRowValue = Cells(currentRow, SourceCol).Value
        If IsEmpty(currentRowValue) Or currentRowValue = "" Then
            Cells(currentRow, SourceCol).Select
            EmptyRow = ActiveCell.Row
            EmptyColumn = ActiveCell.Column
            ProcessInformation (EmptyRow)
            Exit For
        End If
    Next
End Sub


Private Sub ProcessInformation(Row As Integer)
    Dim Column As Integer
    Dim Identifier As String
    'Identifier = TextIdentifier.Text
    Column = 1
    
    Cells(Row, Column).Value = TextName.Text
    Cells(Row, Column + 1).Value = ComboType.Text
    
    Cells(Row, Column + 2).NumberFormat = "@"
    Cells(Row, Column + 2).Value = TextIdentifier.Value
    
    Unload Me
End Sub


Function GetSearchArray(strSearch)
    Dim strResults As String
    Dim DeviceModel As String
    Dim SHT As Worksheet
    Dim rFND As Range
    Dim sFirstAddress
    For Each SHT In ThisWorkbook.Worksheets
        Set rFND = Nothing
        With SHT.UsedRange
            Set rFND = .Cells.Find(What:=strSearch, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlRows, SearchDirection:=xlNext, MatchCase:=False)
            If Not rFND Is Nothing Then
                sFirstAddress = rFND.Address
                Do
                    If DeviceModel = vbNullString And InStr(strSearch, 0) Then
                        If SHT.Name = "Mobiles" Then
                            DeviceModel = ThisWorkbook.Sheets("Mobiles").Cells(rFND.Row, rFND.Column + 3)
                        ElseIf SHT.Name = "Laptops" Then
                            DeviceModel = ThisWorkbook.Sheets("Laptops").Cells(rFND.Row, rFND.Column + 2) & " " & ThisWorkbook.Sheets("Laptops").Cells(rFND.Row, rFND.Column + 3)
                        ElseIf SHT.Name = "4G Services" Then
                            DeviceModel = ThisWorkbook.Sheets("4G Services").Cells(rFND.Row, rFND.Column + 4)
                        ElseIf SHT.Name = "chris" Then
                            DeviceModel = ThisWorkbook.Sheets("chris").Cells(rFND.Row, rFND.Column - 1)
                        End If
                    Else
                        If SHT.Name = "Mobiles" Then
                            DeviceModel = ThisWorkbook.Sheets("Mobiles").Cells(rFND.Row, rFND.Column + 2)
                        ElseIf SHT.Name = "Laptops" Then
                            DeviceModel = ThisWorkbook.Sheets("Laptops").Cells(rFND.Row, rFND.Column + 3) & " " & ThisWorkbook.Sheets("Laptops").Cells(rFND.Row, rFND.Column + 4)
                        ElseIf SHT.Name = "4G Services" Then
                            DeviceModel = ThisWorkbook.Sheets("4G Services").Cells(rFND.Row, rFND.Column + 5)
                        End If
                    End If
                            If strResults = vbNullString Then
                                Sheet = SHT.Name
                                If SHT.Name = "chris" Then
                                    Address = ""
                                Else
                                    Address = " [" & rFND.Address & "]"
                                End If
                                Address = " [" & rFND.Address & "]"
                                strResults = Sheet & " - "
                                strResults = strResults & DeviceModel & Address
                                DeviceModel = vbNullString
                            Else
                                Sheet = SHT.Name
                                If SHT.Name = "chris" Then
                                    Address = ""
                                Else
                                    Address = " [" & rFND.Address & "]"
                                End If
                                strResults = strResults & "|" & Sheet & " - "
                                strResults = strResults & DeviceModel & Address
                                DeviceModel = vbNullString
                            End If
                    Set rFND = .FindNext(rFND)
                Loop While Not rFND Is Nothing And rFND.Address <> sFirstAddress
            End If
        End With
    Next
    If strResults = vbNullString Then
        GetSearchArray = Null
    ElseIf InStr(1, strResults, "|", 1) = 0 Then
        GetSearchArray = Array(strResults)
    Else
        GetSearchArray = Split(strResults, "|")
    End If
    'MsgBox GetSearchArray
End Function