| 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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with if statement
I would try declaring a worksheet variable and assigning to it in the if
statement: Dim ws as Worksheet If tbZipCode.Value < 60000 Then Set ws = Sheet1 Else Set ws = Sheet2 End If With ws HTH -- There are 10 types of people in the world - those who understand binary and those who don't. "aintlifegrand79" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with if statement
Have you tried:
Private Sub cbFindButton_Click() 'Find Rep Info If tbZipCode.Value < 60000 Then With Sheet1 ..select 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 If tbZipCode.Value 60000 Then With Sheet2 ..select 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 Corey.... "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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with if statement
|
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with if statement
I tried this but I get an error that says:
Compile Error: Block If without End IF "Corey ...." wrote: Have you tried: Private Sub cbFindButton_Click() 'Find Rep Info If tbZipCode.Value < 60000 Then With Sheet1 ..select 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 If tbZipCode.Value 60000 Then With Sheet2 ..select 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 Corey.... "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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with if statement
untested, try adding end if's after both end with's
Loop End With End If -- Gary "aintlifegrand79" wrote in message ... I tried this but I get an error that says: Compile Error: Block If without End IF "Corey ...." wrote: Have you tried: Private Sub cbFindButton_Click() 'Find Rep Info If tbZipCode.Value < 60000 Then With Sheet1 ..select 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 If tbZipCode.Value 60000 Then With Sheet2 ..select 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 Corey.... "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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with if statement
This worked but only for the data on sheet 2. When I tried to find a zip
code 60000 I got nothing. "Geoff" wrote: I would try declaring a worksheet variable and assigning to it in the if statement: Dim ws as Worksheet If tbZipCode.Value < 60000 Then Set ws = Sheet1 Else Set ws = Sheet2 End If With ws HTH -- There are 10 types of people in the world - those who understand binary and those who don't. "aintlifegrand79" wrote: 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with if statement
I tried this and it worked but only for zip codes on Sheet2. anytime I enter
a zip code that is <60000 (or on Sheet1) none of the data fills into the form. "Gary Keramidas" wrote: untested, try adding end if's after both end with's Loop End With End If -- Gary "aintlifegrand79" wrote in message ... I tried this but I get an error that says: Compile Error: Block If without End IF "Corey ...." wrote: Have you tried: Private Sub cbFindButton_Click() 'Find Rep Info If tbZipCode.Value < 60000 Then With Sheet1 ..select 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 If tbZipCode.Value 60000 Then With Sheet2 ..select 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 Corey.... "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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with if statement
My guess is your values in tbZipCode are probably text, not numeric. Try
this code instead and see if it works for you... If CLng(tbZipCode.Value) < 60000 Then Set ws = Sheet1 Else Set ws = Sheet2 End If With ws Rick "aintlifegrand79" wrote in message ... This worked but only for the data on sheet 2. When I tried to find a zip code 60000 I got nothing. "Geoff" wrote: I would try declaring a worksheet variable and assigning to it in the if statement: Dim ws as Worksheet If tbZipCode.Value < 60000 Then Set ws = Sheet1 Else Set ws = Sheet2 End If With ws HTH -- There are 10 types of people in the world - those who understand binary and those who don't. "aintlifegrand79" wrote: 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with if statement
Didn't work still got the same problem but thanks for trying.
"Rick Rothstein (MVP - VB)" wrote: My guess is your values in tbZipCode are probably text, not numeric. Try this code instead and see if it works for you... If CLng(tbZipCode.Value) < 60000 Then Set ws = Sheet1 Else Set ws = Sheet2 End If With ws Rick "aintlifegrand79" wrote in message ... This worked but only for the data on sheet 2. When I tried to find a zip code 60000 I got nothing. "Geoff" wrote: I would try declaring a worksheet variable and assigning to it in the if statement: Dim ws as Worksheet If tbZipCode.Value < 60000 Then Set ws = Sheet1 Else Set ws = Sheet2 End If With ws HTH -- There are 10 types of people in the world - those who understand binary and those who don't. "aintlifegrand79" wrote: 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with if statement
|
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with if statement
Send your workbook to my address below if you want me to take a look.
Reference this or copy the snippets to a page in the wb. -- Don Guillett Microsoft MVP Excel SalesAid Software "aintlifegrand79" wrote in message ... 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 |
| Reply |
| 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 | |||
Linear Mode
