finding data across multiple ws to populate a userform (Need Help)

[aintlifegrand79]

My problem is that I have a code that searches for dat on multiple pages but
has multiple entries that maybe the same under the first search condition
therefore my code needs to check a secondary search condition but when my
code tries to do this on some pages it doesn't work. So here is how it
works, I have a userform (ufRepInfo) which has many text/check boxes on it
that are populated from 5 different worksheets (Sheet1 (Zip Codes
00000-19999), Sheet2 (Zip Codes 20000-39999), Sheet3 (Zip Codes 40000-59999),
Sheet4 (Zip Codes 60000-79999), Sheet5 (Zip Codes 80000-99999)). Also on the
userform (ufRepInfo) is a textbox (tbZipCode), a combobox (cbMarket), and a
commandbutton (cbFindButton). The goal is that when the user enters a zip
code into (tbZipCode) then chooses 1 of 5 markets in (cbMarket) and clicks
(cbFindButton) the code should first search for the zip code on the correct
sheet, there might be multiple entries for one zip code, then it should
search within those entries to find the entry for that zip code that has a
"x" in the row that corresponds to the selected market (cbMarket). My code
was working when I had just 2 worksheets but as I have gone along entering
data I have found that I had about 3 entries per zip code and needed put the
data on 5 worksheets. I have no problem populating the userform (ufRepInfo)
if their is only 1 entry for a zip code but when their is multiple entries it
doesn't populate. Hope this makes sense and thanks for your help. 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 Then
Set ws = Sheet2
ElseIf tbZipCode.Value < 60000 Then
Set ws = Sheet3
ElseIf tbZipCode.Value < 80000 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]

If tbZipCode.Value < 20000 Then
Set ws = sheets("Sheet1")
ElseIf tbZipCode.Value < 40000 Then
Set ws = sheets("Sheet2")
ElseIf tbZipCode.Value < 60000 Then
Set ws = sheets("Sheet3")
ElseIf tbZipCode.Value < 80000 Then
Set ws = sheets("Sheet4")
ElseIf tbZipCode.Value >= 80000 Then
Set ws = sheets("Sheet5")

 

[aintlifegrand79]

When I do this joel I get the debugger. It highlights the line
Set ws = Sheets("Sheet_") with the _ being whatever page corresponds to
the zip code entered.

Thank you for helping.

 

[Joel]

The sheet names need to be changed to match the names on the TAB at the
bottom of each sheet. Th esheet names is the character string between the
double quotes in the SET statement.

 

[aintlifegrand79]

Joel this works for all pages when I have only one entry for a zip code and
for some zip codes were I have multiple entries. However, it doesn't work
for all zip codes with multiple entries and it is not that one page works and
others don't but that on some pages it won't populate the userform if the zip
code has multiple entries and on some pages some of the zip codes with
multiple entries will work and some won't. I can't figure out what is wrong,
I have checked to make sure I have the right tab names in but it must be
something in my code that is acting funky.

 

[Joel]

See code changes and comments below.

Select Case 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 Is >= 80000
Set ws = Sheet5
End Select

With ws

Select Case cbMarket
Case "Industrial Drives"
cbMarketCol = "R"
Case "Municipal Drives (W&E)"
cbMarketCol = "S"
Case "HVAC"
cbMarketCol = "T"
Case "Electric Utility"
cbMarketCol = "U"
Case "Oil and Gas"
cbMarketCol = "V"
'---------------------------------------------
' where is W, X, and Y
'---------------------------------------------

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

tbRepNumber.Value = .Range("B" & RowCount).Value
tbRepName.Value = .Range("C" & RowCount).Value
tbRepAddress.Value = .Range("D" & RowCount).Value
tbRepState.Value = .Range("E" & RowCount).Value
tbRepZipCode.Value = .Range("F" & RowCount).Value
tbRepBusPhone.Value = .Range("G" & RowCount).Value
tbRepCellPhone.Value = .Range("H" & RowCount).Value
tbRepFax.Value = .Range("I" & RowCount).Value
tbSAPNumber.Value = .Range("J" & RowCount).Value
tbRegionalManager.Value = .Range("K" & RowCount).Value
tbRMAddress.Value = .Range("L" & RowCount).Value
tbRMState.Value = .Range("M" & RowCount).Value
tbRMZipCode.Value = .Range("N" & RowCount).Value
tbRMBusPhone.Value = .Range("O" & RowCount).Value
tbRMCellPhone.Value = .Range("P" & RowCount).Value
tbRMFax.Value = .Range("Q" & RowCount).Value
If .Range("R" & RowCount).Value = "x" Then
cbIndustrialDrives = True
Else
cbIndustrialDrives = False
End If
If .Range("S" & RowCount).Value = "x" Then
cbMunicipalDrives = True
Else
cbMunicipalDrives = False
End If
If .Range("T" & RowCount).Value = "x" Then
cbHVAC = True
Else
cbHVAC = False
End If
If .Range("U" & RowCount).Value = "x" Then
cbElectricUtility = True
Else
cbElectricUtility = False
End If
If .Range("V" & RowCount).Value = "x" Then
cbOilGas = True
Else
cbOilGas = False
End If
If .Range("W" & RowCount).Value = "x" Then
cbMediumVoltage = True
Else
cbMediumVoltage = False
End If
If .Range("X" & RowCount).Value = "x" Then
cbLowVoltage = True
Else
cbLowVoltage = False
End If
If .Range("Y" & RowCount).Value = "x" Then
cbAfterMarket = True
Else
cbAfterMarket = False
End If
tbInclusions.Value = .Range("Z" & RowCount).Value
tbExclusions.Value = .Range("AA" & RowCount).Value
End If
RowCount = RowCount + 1
Loop
End With

 

[aintlifegrand79]

Joel I think I have figured the problem out. When I have had multiple
entries for a zip code it is really for a large range of zip codes. I have
the row formatted to zip codes but when I dragged the series of zip codes it
is not keeping the actual numbers as zip codes but as 5 digits with a
demicmal place and numbers after it. Basically instead of being 90210 it is
90209.89997891. I guess I am going to try and figure a easy way to change
all the problem zip codes. Thanks for your help