Subscript out of range error

MichaelBedano

New Member
Joined
Apr 9, 2019
Messages
5
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.

Code:
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
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
What is this line supposed to be doing? I would expect a mismatch error
Code:
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
Code:
For Each e In Sheets("Sheet4").Cells(1).Offset(1)
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
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
Code:
For Each e In Sheets("sheet4").Cells(1).Offset(1).Value
to
Code:
For Each e In RCL
 

MichaelBedano

New Member
Joined
Apr 9, 2019
Messages
5
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

New Member
Joined
Apr 9, 2019
Messages
5
BTW here is the revised code.
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,545
Members
414,316
Latest member
ExcelLee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top