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

 

[rlink_23]

I have the Zipcodes formatted as Special "zipcodes" and not zipcode + 4. Then Column B and Column C are formatted as Text

 

[Roderick_E]

I have the Zipcodes formatted as Special "zipcodes" and not zipcode + 4. Then Column B and Column C are formatted as Text

Ok, did you try my new version?

 

[rlink_23]

yes and still not pulling the data. I get the msgbox but nothing else

 

[rlink_23]

I am thoroughly stumped

 

[Roderick_E]

one moment, doing tests on that format type

 

[Roderick_E]

Ok I removed the wildcard feature and it worked fine:
As an added measure, I changed the Zipcode into a value.

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

 

[rlink_23]

That worked!!!! THANK YOU!!!! YASSSSSSS!!! Project almost done!!

 

[rlink_23]

how do I add code when zipcode is blank then it clears City and State?

 

[Roderick_E]

how do I add code when zipcode is blank then it clears City and State?

So, the person presses the button but doesn't put in a zipcode?
How about we just clear both city and state at the moment of button push? That way if something goes wrong, the person isn't seeing incorrect city and state?

Me.State = ""
Me.City = ""
if iserror(application.match(val(Zipcode), sheets("Zips").range("A:A"),0))=false then
matched = application.match(val(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]

It's done by just entering the number in the textbox. Then it populates. I just thought it would be nice if you make a mistake that when you clear the textbox it will clear the other textboxes