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]

Instead of looping through, why not use match? Much more efficient.

if iserror(application.match(Zipcode, sheets("Zips"),0))=false then
matched = application.match(Zipcode, sheets("Zips"),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"
endif

 

[rlink_23]

Thank you Roderick!! I will give that a shot! Stand by and I will let you know if it works!!

 

[AlphaFrog]

Another one...

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] ZipCode_Change()
    [color=darkblue]Dim[/color] v [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]If[/color] Len(ZipCode.Text) >= 2 [color=darkblue]Then[/color]
        [color=darkblue]With[/color] Sheets("Zips")
            v = Application.Match(ZipCode.Text & "*", .Range("A:A"), 0)
            [color=darkblue]If[/color] IsNumeric(v) [color=darkblue]Then[/color]
                Me.State = .Cells(v, "B").Value
                [color=darkblue]If[/color] Len(ZipCode.Text) >= 5 [color=darkblue]Then[/color]
                    Me.City = .Cells(v, "C").Value
                [color=darkblue]Else[/color]
                    Me.City = ""
                [color=darkblue]End[/color] [color=darkblue]If[/color]
            [color=darkblue]Else[/color]
                Me.State = ""
                Me.City = ""
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]Else[/color]
        Me.State = ""
        Me.City = ""
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]

 

[rlink_23]

I get a Run-Time error "1004

Application-defined or object defined error
on this line
If IsError(Application.Match(ZipCode, Sheets("Zips"), 0)) = False Then

 

[rlink_23]

It still doesn't populate the City TextBox nor the City TextBox

 

[Roderick_E]

oops forgot the full range
If IsError(Application.Match(ZipCode, Sheets("Zips").range("A:A"), 0)) = False Then

Alphafrog's solution is more robot and complete. Might try his.

 

[rlink_23]

Ok Roderick, I put the first number of the Zipcode in and instantly I get the msgbox popping up. Id does not even give me a chance to add the whole thing.. lol

 

[rlink_23]

I tried your way and this did not work either

 

[Fluff]

Try using an AfterUpdate rather than a change event

Private Sub ZipCode_AfterUpdate()

End Sub