TheWennerWoman
Active Member
- Joined
- Aug 1, 2019
- Messages
- 270
- Office Version
- 365
- Platform
- Windows
Hello,
I have the following
This has about 400 postcodes (zip codes to our friends across the pond) in a sheet and as the user starts to type into the listbox then the list narrows down (a bit like when you start typing into Google, it's kind a pseudo intelligent approach). It works perfectly.
I've now been asked to enhance this to include every single postcode in the UK - there are 1.7 million of them
I can't put them in a worksheet (too many rows) so I've put them in an array (1.7million rows, 1 column). Array is called myArray.
Can I adapt what I've got to do exactly the same but use the array as the source? I was hoping, to speed things up, to only start intelligently looking after the user has typed in maybe four characters (otherwise it'll just be too slow). Or maybe link to an Access table?
Any thoughts always greatly appreciated.
Thank you for reading.
I have the following
VBA Code:
Dim rRangeCheck As Range
On Error Resume Next
Set rRangeCheck = Range("Postcode_List")
On Error GoTo 0
If rRangeCheck Is Nothing Then
Exit Sub
ElseIf Range("Postcode_List").Count <= 1 Then
Exit Sub
Else
Dim NameList, Entry
ListBox1.Clear
NameList = Range("Postcode_List").Value
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For Each Entry In NameList
If InStr(1, Entry, TextBox1.Value, 1) > 0 Then .Item(Entry) = Entry
Next
If .Count > 0 Then ListBox1.List = .keys
End With
End If
This has about 400 postcodes (zip codes to our friends across the pond) in a sheet and as the user starts to type into the listbox then the list narrows down (a bit like when you start typing into Google, it's kind a pseudo intelligent approach). It works perfectly.
I've now been asked to enhance this to include every single postcode in the UK - there are 1.7 million of them
I can't put them in a worksheet (too many rows) so I've put them in an array (1.7million rows, 1 column). Array is called myArray.
Can I adapt what I've got to do exactly the same but use the array as the source? I was hoping, to speed things up, to only start intelligently looking after the user has typed in maybe four characters (otherwise it'll just be too slow). Or maybe link to an Access table?
Any thoughts always greatly appreciated.
Thank you for reading.