I don't know what I am doing wrong....

[rlink_23]

I have used a code like this in a different part of my userform, To transfer info from a sheet to the userform. I have a list of Zipcodes in a sheet called ("Zips") in Column A is the actual Zip code, the State is in Column B, and City is Column C. What I am trying to do is when the Zip code is entered into TextBox ("Zipcode") It populates TextBox ("City") and TextBox ("State"). I have used a very similar code Elsewhere in my userform but this code WILL NOT work at all.. I know it is trying as it runs super super solw when I am typing in the TextBox ("ZipCode"). I think I have used Dim I As long before so maybe that is cousiung an issue.. idk.. Please help!!! Maybe I am just missing something and am not noticing the smallest detail...

Private Sub ZipCode_Change()Dim I As Long, Lastrow As Long
Lastrow = Sheets("Zips").Range("A" & Rows.Count).End(xlUp).Row
For I = 2 To Lastrow


If Sheets("Zips").Cells(I, "A").Value = ZipCode Or _
Sheets("Zips").Cells(I, "A").Value = Val("ZipCode") Then


Me.State = Sheets("Zips").Cells(I, "B").Value
Me.City = Sheets("Zips").Cells(I, "C").Value






End If


Next






End Sub

 

[Roderick_E]

ah ok duh, you need Zipcode_Exit() not Change. Change is triggering after each character typed in, even before the user finishes putting it all in
Your code will work probably if you move it to Zipcode_Exit or mine or Alphafrog's

 

[rlink_23]

Which code should I use I have three choices now lol

 

[Roderick_E]

Which code should I use I have three choices now lol

Use either yours or AlphaFrogs, but place in Zipcode_AfterUpdate instead of Change

 

[rlink_23]

Tried it and still noting. I seriously don't know why it wont work. I have no clue why... I am getting frustrated when I know the coding works in another part of my userform

 

[rlink_23]

Your code has gotten me close. I input the zipcode and I get the error message that 99201 is not a valid zipcode but it is, and not transferring the other data

 

[rlink_23]

The zipcode number starts in Column A2 state is B2 and City is C2

They end in row 42538

 

[rlink_23]

And I remaned the sheet zips, but in the properties it is called "Sheet6" I will try "Sheet6". There are also a few other sheets so is that causing an issue? I am at a loss

 

[Roderick_E]

The zipcode number starts in Column A2 state is B2 and City is C2

They end in row 42538

I'm suspecting a formatting issue. How is the zipcode column formatted? Text? General? Number?
At any rate, using Zipcode_AfterUpdate use the following modified version of my code


if iserror(application.match("*" & Zipcode & "*", sheets("Zips").range("A:A"),0))=false then
matched = application.match("*" & Zipcode & "*", sheets("Zips").range("A:A"),0)
Me.State = Sheets("Zips").Cells(matched, "B").Value
Me.City = Sheets("Zips").Cells(matched, "C").Value
else
msgbox Zipcode & " not found in Zips list.", vbcritical, "ALERT"
exit sub
endif

 

[rlink_23]

I can change it to anything. What should I format it too?

 

[Roderick_E]

I can change it to anything. What should I format it too?

Typically you'll want a zipcode column to be TEXT due to potential leading zeros.