Problem with find function

[aintlifegrand79]

I have a userform that fill with rep information based on the zip code and
the market a user selects. There are more than one rep for every zip code,
therefore my code should check for a zip code first and then once it finds a
zip code it should find the rep info that corresponds to that zip code and
the market selected. Unfortunately my code does not quite work right. I
have over 200,000 entries therefore I have had to spread the data out over 5
worksheets. The problem is that in my code when I have a just it where a zip
code under >20000 my code works perfectly and finds the rep info no matter
how many of the same zip codes entries there are. However, when I have my
code where a zip code is greater tahn or equal to =<200000 but less than

400000 it will only find the rep info when there is only 1 entry per zip

code. Thanks for any help I can get, here is my code.

Private Sub cbFindButton_Click()
'Find Rep Info
Dim ws As Worksheet

If tbZipCode.Value < 20000 Then
Set ws = Sheet1
ElseIf tbZipCode.Value < 40000 And tbZipCode.Value >= 20000 Then
Set ws = Sheet2
ElseIf tbZipCode.Value < 60000 And tbZipCode.Value >= 40000 Then
Set ws = Sheet3
ElseIf tbZipCode.Value < 80000 And tbZipCode.Value >= 60000 Then
Set ws = Sheet4
ElseIf tbZipCode.Value >= 80000 Then
Set ws = Sheet5
End If
With ws

Select Case cbMarket
Case "Industrial Drives"
cbMarketCol = 18
Case "Municipal Drives (W&E)"
cbMarketCol = 19
Case "HVAC"
cbMarketCol = 20
Case "Electric Utility"
cbMarketCol = 21
Case "Oil and Gas"
cbMarketCol = 22
End Select
RowCount = 1
Do While .Range("A" & RowCount) <> ""
If .Range("A" & RowCount) = Val(tbZipCode.Value) And _
.Cells(RowCount, cbMarketCol) <> "" Then

Set Rep = .Range("A" & RowCount)
tbRepNumber.Value = Rep.Offset(0, 1).Value
tbRepName.Value = Rep.Offset(0, 2).Value
tbRepAddress.Value = Rep.Offset(0, 3).Value
tbRepState.Value = Rep.Offset(0, 4).Value
tbRepZipCode.Value = Rep.Offset(0, 5).Value
tbRepBusPhone.Value = Rep.Offset(0, 6).Value
tbRepCellPhone.Value = Rep.Offset(0, 7).Value
tbRepFax.Value = Rep.Offset(0, 8).Value
tbSAPNumber.Value = Rep.Offset(0, 9).Value
tbRegionalManager.Value = Rep.Offset(0, 10).Value
tbRMAddress.Value = Rep.Offset(0, 11).Value
tbRMState.Value = Rep.Offset(0, 12).Value
tbRMZipCode.Value = Rep.Offset(0, 13).Value
tbRMBusPhone.Value = Rep.Offset(0, 14).Value
tbRMCellPhone.Value = Rep.Offset(0, 15).Value
tbRMFax.Value = Rep.Offset(0, 16).Value
If Rep.Offset(0, 17).Value = "x" Then cbIndustrialDrives = True
If Rep.Offset(0, 18).Value = "x" Then cbMunicipalDrives = True
If Rep.Offset(0, 19).Value = "x" Then cbHVAC = True
If Rep.Offset(0, 20).Value = "x" Then cbElectricUtility = True
If Rep.Offset(0, 21).Value = "x" Then cbOilGas = True
If Rep.Offset(0, 22).Value = "x" Then cbMediumVoltage = True
If Rep.Offset(0, 23).Value = "x" Then cbLowVoltage = True
If Rep.Offset(0, 24).Value = "x" Then cbAfterMarket = True
tbInclusions.Value = Rep.Offset(0, 25).Value
tbExclusions.Value = Rep.Offset(0, 26).Value
End If
RowCount = RowCount + 1
Loop
End With
End Sub

 

[Joel]

make these changes

from
Set ws = Sheet1
Set ws = Sheet2
Set ws = Sheet3
Set ws = Sheet4
Set ws = Sheet5

to
Set ws = Sheets("Sheet1")
Set ws = Sheets("Sheet2")
Set ws = Sheets("Sheet3")
Set ws = Sheets("Sheet4")
Set ws = Sheets("Sheet5")

 

[Dave Peterson]

You don't need those and's in your code. If you've already passed one criteria,
you don't need to check it again.

If tbZipCode.Value < 20000 Then
Set ws = Sheet1
ElseIf tbZipCode.Value < 40000 then
Set ws = Sheet2
ElseIf tbZipCode.Value < 60000 then
Set ws = Sheet3
ElseIf tbZipCode.Value < 80000 Then
Set ws = Sheet4
Else
Set ws = Sheet5
End If

VBA is pretty forgiving. It'll compare text (from the textbox tbzipcode) with
numbers and give you what you expect.

But I'd still check to make sure that the textbox was a numeric/valid entry.

You may find using select case easier to read:

set ws = nothing
if isnumeric(tbzipcode.value) then
select case clng(tbzipcode.value)
case is < 20000: set ws = sheet1
case is < 40000: set ws = sheet2
case is < 60000: set ws = sheet3
case is < 80000: set ws = sheet4
case else: set ws = sheet5
end select
end if

if ws is nothing then
'somthing very bad happened!
'return an error and get out???
end if

=======================
As for your real question, I'd look at the data.

I'd bet that there were actual differences that made this line:

If .Range("A" & RowCount) = Val(tbZipCode.Value) And _
.Cells(RowCount, cbMarketCol) <> "" Then

take the Else branch.