Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with if statement
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with If Statement | Excel Worksheet Functions | |||
Problem with IF statement | Excel Worksheet Functions | |||
for..next statement problem | Excel Programming | |||
If Statement Problem | Excel Discussion (Misc queries) | |||
IF Statement Problem | Excel Worksheet Functions |