Lookup for postcode module error

[Patriot2879]

Hi I hopeyou can help, I am trying to make a post code lookup, so for example inTextBox51 you type in your postcode like ‘WR5 3’ then automatically it updates textbox52 with the area of the postcode for example ‘Worcester’. It gets the data from my sheet called ‘PostCode’and looks at row A for the Postcode and row B is the area, I have made a module below called ‘GetData’ but it is not working, and In the sub for textbox51 I have put in GetData for it to goto the module.

My codingis below hope you can help.

Option Explicit

[FONT=Calibri][SIZE=3][COLOR=#000000]Dim id As String, i As Long, j As Integer, flag As Boolean[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sub GetData()
   Dim Rw As Variant
   If Len(UserForm1.TextBox51.Value) > 0 Then
      
      flag = False
      id = UserForm1.TextBox51.Value
      Rw = Application.Match(id, Sheets("PostCode").Range("A2:A9316"), 0)
      
      If Not IsError(Rw) Then
         flag = True
         For j = 1 To 1
            UserForm1.Controls("TextBox52" & j).Value = ThisWorkbook.Worksheets("PostCode").Cells(Rw, j).Value
         Next j
      End If
      
      If flag = False Then
         For j = 1 To 1
            UserForm1.Controls("TextBox52" & j).Value = ""
         Next j
      End If
      
   Else
     
   End If
End Sub[/COLOR][/SIZE][/FONT]

 

[RoryA]

What/where is the actual error?

 

[Patriot2879]

Hi I get a yellow arrow pointing at the snippet below.

UserForm1.Controls("TextBox52" & j).Value = ""

 

[Akuini]

Untested, but try this:

Sub GetData()
   Dim Rw As Variant
   With UserForm1
        If Len(.TextBox51.Value) > 0 Then
          
           Rw = Application.Match(.TextBox51.Value, Sheets("PostCode").Range("[COLOR=#ff0000]A1[/COLOR]:A9316"), 0)
           
           If Not IsError(Rw) Then
                 .TextBox52.Value = Sheets("PostCode").Cells(Rw, "B").Value
           Else
                 .TextBox52.Value = ""
           End If
          
        End If
   End With
End Sub

It's better if you use a command button to call Sub GetData, like this (assuming Sub GetData is in Module1):

Private Sub CommandButton1_Click()
Call Module1.GetData
End Sub

 

[Patriot2879]

That works great thank you, where did I go wrong?

 

[Kyle123]

For some reason you are looping here:

For j = 1 To 1
            UserForm1.Controls("TextBox52" & j).Value = ""

Which means that you are trying to access Textbox521 which doesn't exist in your userform.

 

[Patriot2879]

ok thank you for that is that what the '& j' is?

 

[Akuini]

1. You don't need loop to insert the value in textbox52

2. if j=1, then ("TextBox52" & j) means "TextBox521", that's what cause the error

3. this line:
Rw = Application.Match(id, Sheets("PostCode").Range("A2:A9316"), 0)
since you begin at Range("A2") then if rw = 1 means row 2 not 1

so with this line:
ThisWorkbook.Worksheets("PostCode").Cells(Rw, j).Value

the row will off by 1, so I change Range("A2:A9316") to Range("A1:A9316")

 

[Patriot2879]

that is brilliant I understand where I went wrong, thank you for your help, much appreciated.

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.