Subscript out of range error

[MichaelBedano]

Hi Everyone I have a code that suppose to make the combobox in the userform searchable found this code in the internet but the subscript error out of range occurred. I already changed the worksheet name to the existing worksheet but still there is an error. can anyone please help me resolved the problem below is the code that I used in the combobox.
My list that should be in the drop is in Sheet4 and in column a with multiple rows. Thanks in advance for the help.

Private Sub KgArec3_Change()    Dim e, temp
    With Me
        temp = .KgArec3.Value
        If Not .KgArec3.MatchFound Then
            .KgArec3.Clear
            If Len(temp) Then
                For Each e In Sheets("sheet4").Cells(1).Offset(1).Value
                    If (e <> "") * (e Like "*" & temp & "*") Then
                        .KgArec3.AddItem e
                    End If
                Next
                If .KgArec3.ListCount > 0 Then
                    .KgArec3.ListIndex = 0
                End If
            End If
        End If
    End With
End Sub

 

[Yongle]

What is this line supposed to be doing? I would expect a mismatch error

For Each e In Sheets("Sheet4").Cells(1).Offset(1).Value

Sheets("Sheet4").Cells(1).Offset(1) is always cell A2

It should not error like this - but it is only ever looking at cell A2

For Each e In Sheets("Sheet4").Cells(1).Offset(1)

 

[MichaelBedano]

Hi Yongle should get the list and place it in the dropdown. How can I correct the code?

 

[Yongle]

Where is the list? Is it in column A starting at row 2. If so

Declare and set the Range Containing List earlier in the code

    Dim e As Range, [COLOR=#b22222]RCL[/COLOR] As Range
    With Sheets("Sheet4")
        Set RCL = [SIZE=3][B].[/B][/SIZE]Range("A2", [SIZE=3][B].[/B][/SIZE]Range("A" & Rows.Count).End(xlUp))
    End With

and change

For Each e In Sheets("sheet4").Cells(1).Offset(1).Value

to

For Each e In RCL

 

[MichaelBedano]

Hi Yongle it is working but it is a bit odd. When I enter a letter it automatically show the first item in the list. what I want is make searchable like for example
I have this on my list:

A2 Acacia Hotel
A3 Crimson Hotel
A4 Genosi
A5 DAP
A6 Coffee Gounds

When I enter the word hotel only the Acacia Hotel and Crimson Hotel will be shown in the dropdown because they have hotel on their name. or when I enter letter A the DAP and Acacia Hotel will only show in the dropdown because they each have letter A. Hope you can help me. Thank you very much.

 

[MichaelBedano]

BTW here is the revised code.

Private Sub KgArec3_Change()   
 Dim e As Range, RCL As Range, temp
    With Sheets("ListOfAccounts")
        Set RCL = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    With Me
        temp = .KgArec3.Value
        If Not .KgArec3.MatchFound Then
            .KgArec3.Clear
            If Len(temp) Then
                For Each e In RCL
                    If (e <> "") * (e Like "*" & temp & "*") Then
                        .KgArec3.AddItem e
                    End If
                Next
                If .KgArec3.ListCount > 0 Then
                    .KgArec3.ListIndex = 0
                End If
            End If
        End If
       End With
    End With
End Sub