I have a database that holds a ton of data in it and continues to grow at
astonishing rate. Therefore I have had to add extra sheets to hold the data
but my code has also had change to accomadate for all this new data.
Basically my database uses a userform to look up rep information and
populate that userform based on a zip code and a market selected by the user.
My problem lies in the fact that their can be multiple reps for the same zip
code and therefore I have well over 200,000 entries in my database. To
accomodate for this problem I have made it so all Zip codes
between(00000-19999) are on Worksheet 1, all zip codes between (20000 -
39999) are on Worksheet 2, all zip codes between (40000-59999) are on
Worksheet 3, all zip codes between (60000-79999) are on Worksheet 4, and all
zip codes between (80000-99999) are on Worksheet 5. My current code however
is only written for two worksheets with worksheet 1 having all zip codes less
than 50000 on it and Worksheet 2 having everything greater than or equal to
50000. Here is my code.

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

If tbZipCode.Value < 50000 Then
Set ws = Sheet1
Else
Set ws = Sheet2
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