Don I did try your idea but it didn't work. I am not sure I understood how
to use it in conjunction with my code.

"Don Guillett" wrote:

Modify this idea to suit

Sub whichsheet()
tbzipcode = Sheets("Sheet1").Range("a1")
If tbzipcode 6000 Then
mysheet = "sheet3"
Else
mysheet = "sheet2"
End If
With Sheets(mysheet)
MsgBox .Range("c1").Value
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"aintlifegrand79" wrote in
message ...
I have a userform that is populated by entering a zip code into the textbox
(tbZipCode) and choosing a market from the combobox (cbMarkets) and
clicking
the find button. The way the code works is it first looks up a Zip Code
in
Sheet 1 of the database and then looks for the zip code that also
corresponds
with the market selected. My problem is that there are 99999 zipcodes so
I
need to use 2 sheets. I have set it up that all zip codes below 60000 are
on
Sheet1 and all above 60000 are on Sheet2 but I keep getting an error
message
for my code. What am I doing wrong, thanks for your help. The code works
for just sheet 1 if you replace the first two lines of code with: With
Sheet1

Private Sub cbFindButton_Click()
'Find Rep Info
If tbZipCode.Value < 60000 Then With Sheet1
If tbZipCode.Value 60000 Then With Sheet2
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