Very simple InputBox problem

[Rebuild8]

I have an input box where zip codes are entered, and in order for it to work with my code, I had to "Dim ZipCode as Long". The problem is there may be times when text is entered (for example, "South America" or "Europe") instead of a zip code. How can I change the code so that if someone enters text, it will look for text in column B rather than a number?

Sub GetData()
Dim ZipCode As Long
ZipCode = InputBox("Enter a zip code and click OK", "Zip Code Lookup")
With Worksheets("Field Data")
Temp = 0
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If ZipCode >= .Range("B" & i) And ZipCode <= .Range("C" & i) Then
MsgBox "Region: " & .Range("A" & i) & vbNewLine & "Contact: " & .Range("D" & i) _
& vbNewLine & "Email: " & .Range("E" & i), , "Zip Code Lookup"
Temp = 1
i = LastRow
End If
Next i
End With
If Temp = 0 Then
MsgBox "No data found matching that zip code", , "Zip Code Lookup"
End If
End Sub

 

[GTO]

Just a stab / not tested, but if you are saying that we might want to test if a string if greater/less than another string, OR, we may be comparing numbers... Try changing ZipCode to As String and use the .Text property of the cells being looked at.

 

[gregtx81]

Here's a rough start. Note that there are a lot of options you can set for the Range.Find method. Also, may need consider duplicates, slight misspellings, etc.

Sub aaa()

Dim ZipCode As Long
Dim inputvalue As Variant

inputvalue = InputBox("Enter a zip code and click OK", "Zip Code Lookup")

Dim bLongValueEntered As Boolean
bLongValueEntered = True

On Error Resume Next
ZipCode = inputvalue
If Err.Number <> 0 Then 'if above line caused error
    bLongValueEntered = False
    Err.Clear
End If
On Error GoTo 0 'disables on error resume next

Temp = 0
LastRow = Range("B" & Rows.Count).End(xlUp).Row


If bLongValueEntered Then
    'your code for zipcode search
    'your code for zipcode search
    
Else
    Set myrange = Range("B2:B" & LastRow)
    Set r = myrange.Find(What:=inputvalue)
    If Not r Is Nothing Then
        MsgBox "input value found in cell " & r.Address
    Else: MsgBox inputvalue & " " & "was not found"
    End If
End If
End Sub

 

[vds1]

inputvalue = InputBox("Enter a zip code and click OK", "Zip Code Lookup")
If IsNumeric(inputvalue) = False Then
Range("B:B").Select
Selection.Find(What:=inputvalue).Activate
DataWanted = ActiveCell.Value
Else
Range("A:A").Select 'Assuming column A has zip code
Selection.Find(What:=inputvalue).Activate
DataWanted = ActiveCell.Value
'you can navigate cells using offset property
End If