[Solved] Creating A Search Box Utilizing VBA Code

B

brandy.chapman

New member
Hi! I watched your video and read through your article in regards to how to create a search box utilizing VBA code.
This video was very informative and helped me understand the code well enough to apply it to my own conditions, outside of one area. Line item under PartialMatch "For i = 1 To Len(Value2)" I am getting an Error 13 of mixed match type. I've read through some forums and tried to understand the error code, but can't figure out where inside the code this would trigger an issue with my spreadsheet.

Sub SearchMultipleSheets()
Main_Sheet = "CommunitySearch"
Search_Cell = "B3"
SearchType_Cell = "C3"
Paste_Cell = "B9"
Searched_Sheets = Array("Master", "LandDev", "StartSalesClosings", "Entitlements", "LDScheduleDates", "LDActualDates", "Variances")
Searched_Ranges = Array("A2:Z250", "A2:Z250", "A2:Z250", "A2:Z250", "A2:Z250", "A2:Z250", "A2:Z250")
Copy_Format = True
Last_Row = Sheets(Main_Sheet).Range(Paste_Cell).End(xlDown).Row
Last_Column = Sheets(Main_Sheet).Range(Paste_Cell).End(xlToRight).Column
Set Used_Range = Sheets(Main_Sheet).Range(Cells(Range(Paste_Cell).Row, Range(Paste_Cell).Column), Cells(Last_Row, Last_Column))
Used_Range.ClearContents
Used_Range.ClearFormats
Value1 = Sheets(Main_Sheet).Range(Search_Cell).Value
Count = -1
If Sheets(Main_Sheet).Range(SearchType_Cell).Value = "Case-Sensitive" Then
Case_Sensitive = True
ElseIf Sheets(Main_Sheet).Range(SearchType_Cell).Value = "Case-Insensitive" Then
Case_Sensitive = False
Else
MsgBox ("Choose a Search Type.")
Exit Sub
End If
For S = LBound(Searched_Sheets) To UBound(Searched_Sheets)
Set Rng = Sheets(Searched_Sheets(S)).Range(Searched_Ranges(S))
For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
Value2 = Rng.Cells(i, j).Value
If PartialMatch(Value1, Value2, Case_Sensitive) = True Then
Count = Count + 1
Rng.Rows(i).Copy
Set Paste_Range = Sheets(Main_Sheet).Cells(Range(Paste_Cell).Row + Count, Range(Paste_Cell).Column)
If Copy_Format = True Then
Paste_Range.PasteSpecial Paste:=xlPasteAll
Else
Paste_Range.PasteSpecial Paste:=xlPasteValues
End If
End If
Next j
Next i
Next S
Application.CutCopyMode = False
End Sub

Function PartialMatch(Value1, Value2, Case_Sensitive)
Matched = False
For i = 1 To Len(Value2)
If Case_Sensitive = True Then
If Mid(Value2, i, Len(Value1)) = Value1 Then
Matched = True
Exit For
End If
Else
If Mid(LCase(Value2), i, Len(Value1)) = LCase(Value1) Then
Matched = True
Exit For
End If
End If
Next i
PartialMatch = Matched
End Function
 

Attachments

brandy.chapman said:
Hi! I watched your video and read through your article in regards to how to create a search box utilizing VBA code.
This video was very informative and helped me understand the code well enough to apply it to my own conditions, outside of one area. Line item under PartialMatch "For i = 1 To Len(Value2)" I am getting an Error 13 of mixed match type. I've read through some forums and tried to understand the error code, but can't figure out where inside the code this would trigger an issue with my spreadsheet.

Sub SearchMultipleSheets()
Main_Sheet = "CommunitySearch"
Search_Cell = "B3"
SearchType_Cell = "C3"
Paste_Cell = "B9"
Searched_Sheets = Array("Master", "LandDev", "StartSalesClosings", "Entitlements", "LDScheduleDates", "LDActualDates", "Variances")
Searched_Ranges = Array("A2:Z250", "A2:Z250", "A2:Z250", "A2:Z250", "A2:Z250", "A2:Z250", "A2:Z250")
Copy_Format = True
Last_Row = Sheets(Main_Sheet).Range(Paste_Cell).End(xlDown).Row
Last_Column = Sheets(Main_Sheet).Range(Paste_Cell).End(xlToRight).Column
Set Used_Range = Sheets(Main_Sheet).Range(Cells(Range(Paste_Cell).Row, Range(Paste_Cell).Column), Cells(Last_Row, Last_Column))
Used_Range.ClearContents
Used_Range.ClearFormats
Value1 = Sheets(Main_Sheet).Range(Search_Cell).Value
Count = -1
If Sheets(Main_Sheet).Range(SearchType_Cell).Value = "Case-Sensitive" Then
Case_Sensitive = True
ElseIf Sheets(Main_Sheet).Range(SearchType_Cell).Value = "Case-Insensitive" Then
Case_Sensitive = False
Else
MsgBox ("Choose a Search Type.")
Exit Sub
End If
For S = LBound(Searched_Sheets) To UBound(Searched_Sheets)
Set Rng = Sheets(Searched_Sheets(S)).Range(Searched_Ranges(S))
For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
Value2 = Rng.Cells(i, j).Value
If PartialMatch(Value1, Value2, Case_Sensitive) = True Then
Count = Count + 1
Rng.Rows(i).Copy
Set Paste_Range = Sheets(Main_Sheet).Cells(Range(Paste_Cell).Row + Count, Range(Paste_Cell).Column)
If Copy_Format = True Then
Paste_Range.PasteSpecial Paste:=xlPasteAll
Else
Paste_Range.PasteSpecial Paste:=xlPasteValues
End If
End If
Next j
Next i
Next S
Application.CutCopyMode = False
End Sub

Function PartialMatch(Value1, Value2, Case_Sensitive)
Matched = False
For i = 1 To Len(Value2)
If Case_Sensitive = True Then
If Mid(Value2, i, Len(Value1)) = Value1 Then
Matched = True
Exit For
End If
Else
If Mid(LCase(Value2), i, Len(Value1)) = LCase(Value1) Then
Matched = True
Exit For
End If
End If
Next i
PartialMatch = Matched
End Function
Click to expand...
Hello Brandy

Thanks for your wonderful compliment! Your appreciation means a lot to us.

I am delighted to inform you that I have reviewed your problem and found a solution for you. Please check the following:

Creating A Search Box Utilizing VBA Code.gif​

Error 13 Type Mismatch in VBA typically occurs when you try to perform an operation on incompatible data types. Thanks a lot for sharing the workbook; it helps me to investigate your problem deeply.

When debugging your code, I have seen that for some loops, the Value2 variable contains Error 2042, which is the VBA error code for a #N/A Error. So, I come up with a solution to avoid such a situation. Before assigning any value to the Value2 variable, I check for any error values using the IsError function. Besides, when pasting, I use xlPasteValuesAndNumberFormats.

Use the following VBA code:
Code: 
Sub SearchMultipleSheets()

    Main_Sheet = "CommunitySearch"
    Search_Cell = "B3"
    SearchType_Cell = "C3"
    Paste_Cell = "B9"
    Searched_Sheets = Array("Master", "LandDev", "StartSalesClosings", "Entitlements", "LDScheduleDates", "LDActualDates", "Variances")
    Searched_Ranges = Array("A2:Z250", "A2:Z250", "A2:Z250", "A2:Z250", "A2:Z250", "A2:Z250", "A2:Z250")
    Copy_Format = True
    Last_Row = Sheets(Main_Sheet).Range(Paste_Cell).End(xlDown).Row
    Last_Column = Sheets(Main_Sheet).Range(Paste_Cell).End(xlToRight).Column
    Set Used_Range = Sheets(Main_Sheet).Range(Cells(Range(Paste_Cell).Row, Range(Paste_Cell).Column), Cells(Last_Row, Last_Column))
    Used_Range.ClearContents
    Used_Range.ClearFormats
    Value1 = Sheets(Main_Sheet).Range(Search_Cell).Value
    Count = -1
    If Sheets(Main_Sheet).Range(SearchType_Cell).Value = "Case-Sensitive" Then
        Case_Sensitive = True
    ElseIf Sheets(Main_Sheet).Range(SearchType_Cell).Value = "Case-Insensitive" Then
        Case_Sensitive = False
    Else
        MsgBox ("Choose a Search Type.")
        Exit Sub
    End If
    For S = LBound(Searched_Sheets) To UBound(Searched_Sheets)
        Set Rng = Sheets(Searched_Sheets(S)).Range(Searched_Ranges(S))
        For i = 1 To Rng.Rows.Count
            For j = 1 To Rng.Columns.Count
                If Not IsError(Rng.Cells(i, j).Value) Then
                    Value2 = Rng.Cells(i, j).Value
                    If PartialMatch(Value1, Value2, Case_Sensitive) = True Then
                        Count = Count + 1
                        Rng.Rows(i).Copy
                        Set Paste_Range = Sheets(Main_Sheet).Cells(Range(Paste_Cell).Row + Count, Range(Paste_Cell).Column)
                        Paste_Range.PasteSpecial xlPasteValuesAndNumberFormats
                    End If
                End If
            Next j
        Next i
    Next S
    Application.CutCopyMode = False
   
End Sub

Function PartialMatch(Value1, Value2, Case_Sensitive)

    Matched = False
    For i = 1 To Len(Value2)
        If Case_Sensitive = True Then
            If Mid(Value2, i, Len(Value1)) = Value1 Then
                Matched = True
                Exit For
            End If
        Else
            If Mid(LCase(Value2), i, Len(Value1)) = LCase(Value1) Then
                Matched = True
                Exit For
            End If
        End If
    Next i
    PartialMatch = Matched
   
End Function

I have suggestions for you as well. When testing the code, I found that CommunitySearch is not being cleared properly. This clearing feature can be improved.

Hopefully, you have found the ideas you were looking for. Good luck.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy
 
Lutfor Rahman Shimanto said:
Hello Brandy

Thanks for your wonderful compliment! Your appreciation means a lot to us.

I am delighted to inform you that I have reviewed your problem and found a solution for you. Please check the following:


Error 13 Type Mismatch in VBA typically occurs when you try to perform an operation on incompatible data types. Thanks a lot for sharing the workbook; it helps me to investigate your problem deeply.

When debugging your code, I have seen that for some loops, the Value2 variable contains Error 2042, which is the VBA error code for a #N/A Error. So, I come up with a solution to avoid such a situation. Before assigning any value to the Value2 variable, I check for any error values using the IsError function. Besides, when pasting, I use xlPasteValuesAndNumberFormats.

Use the following VBA code:
Code: 
Sub SearchMultipleSheets()

    Main_Sheet = "CommunitySearch"
    Search_Cell = "B3"
    SearchType_Cell = "C3"
    Paste_Cell = "B9"
    Searched_Sheets = Array("Master", "LandDev", "StartSalesClosings", "Entitlements", "LDScheduleDates", "LDActualDates", "Variances")
    Searched_Ranges = Array("A2:Z250", "A2:Z250", "A2:Z250", "A2:Z250", "A2:Z250", "A2:Z250", "A2:Z250")
    Copy_Format = True
    Last_Row = Sheets(Main_Sheet).Range(Paste_Cell).End(xlDown).Row
    Last_Column = Sheets(Main_Sheet).Range(Paste_Cell).End(xlToRight).Column
    Set Used_Range = Sheets(Main_Sheet).Range(Cells(Range(Paste_Cell).Row, Range(Paste_Cell).Column), Cells(Last_Row, Last_Column))
    Used_Range.ClearContents
    Used_Range.ClearFormats
    Value1 = Sheets(Main_Sheet).Range(Search_Cell).Value
    Count = -1
    If Sheets(Main_Sheet).Range(SearchType_Cell).Value = "Case-Sensitive" Then
        Case_Sensitive = True
    ElseIf Sheets(Main_Sheet).Range(SearchType_Cell).Value = "Case-Insensitive" Then
        Case_Sensitive = False
    Else
        MsgBox ("Choose a Search Type.")
        Exit Sub
    End If
    For S = LBound(Searched_Sheets) To UBound(Searched_Sheets)
        Set Rng = Sheets(Searched_Sheets(S)).Range(Searched_Ranges(S))
        For i = 1 To Rng.Rows.Count
            For j = 1 To Rng.Columns.Count
                If Not IsError(Rng.Cells(i, j).Value) Then
                    Value2 = Rng.Cells(i, j).Value
                    If PartialMatch(Value1, Value2, Case_Sensitive) = True Then
                        Count = Count + 1
                        Rng.Rows(i).Copy
                        Set Paste_Range = Sheets(Main_Sheet).Cells(Range(Paste_Cell).Row + Count, Range(Paste_Cell).Column)
                        Paste_Range.PasteSpecial xlPasteValuesAndNumberFormats
                    End If
                End If
            Next j
        Next i
    Next S
    Application.CutCopyMode = False
  
End Sub

Function PartialMatch(Value1, Value2, Case_Sensitive)

    Matched = False
    For i = 1 To Len(Value2)
        If Case_Sensitive = True Then
            If Mid(Value2, i, Len(Value1)) = Value1 Then
                Matched = True
                Exit For
            End If
        Else
            If Mid(LCase(Value2), i, Len(Value1)) = LCase(Value1) Then
                Matched = True
                Exit For
            End If
        End If
    Next i
    PartialMatch = Matched
  
End Function

I have suggestions for you as well. When testing the code, I found that CommunitySearch is not being cleared properly. This clearing feature can be improved.

Hopefully, you have found the ideas you were looking for. Good luck.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy
Click to expand...
This is fantastic information! Thank you so much for taking a look at the document for me. I agree, there are definitely some improvements now that the code is able to fully run and I can see the return values. I am going to dig into it a little further and see what I can come up with. Hoping I can reach back out if I run into any more bumps along the way!
Many thanks,
Brandy
 
brandy.chapman said:
This is fantastic information! Thank you so much for taking a look at the document for me. I agree, there are definitely some improvements now that the code is able to fully run and I can see the return values. I am going to dig into it a little further and see what I can come up with. Hoping I can reach back out if I run into any more bumps along the way!
Many thanks,
Brandy
Click to expand...
Dear Brandy

Thanks for your compliments! You are very welcome.

YES, absolutely. Don't hesitate to reach out again with further queries.

Regards
ExcelDemy
 
You must log in or register to reply here.

Online statistics

Members online
0
Guests online
9
Total visitors
9
Totals may include hidden visitors.

Forum statistics

Threads
352
Messages
1,541
Members
652
Latest member
William Tang

Share this page

Back
Top