| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Greater than less than in a find function
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Greater than less than in a find function
The first two sentences of your question suggest the solution is Access
best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "aintlifegrand79" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Greater than less than in a find function
See if this will work:
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 < 100000 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 "aintlifegrand79" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Greater than less than in a find function
That didn't work, but thank you. In most situations this code is not
populating the userform but I have tried it a number of times and very irregularly it fills the fields on the userform. Thanks again I really appreciate the help. "JLGWhiz" wrote: See if this will work: 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 < 100000 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 "aintlifegrand79" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Greater than less than in a find function
If it would make it any easier I could probably do with only 3 worksheets but
I would prefer to use 5 worksheets. "aintlifegrand79" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Greater than less than in a find function
I switched the code up a bit and got it to work, but now my problem is that
if there is more than one entry for the same zip code then it won't return any information but if there is only one entry for the zip code it works perfectly. I have no clue why my code does not differentiated between the multiple zip codes based on the market selected. Here is the code as it looks now. Private Sub cbFindButton_Click() 'Find Rep Info Dim ws As Worksheet If tbZipCode.Value < 20000 Then Set ws = Sheet1 ElseIf tbZipCode.Value < 40000 And tbZipCode.Value = 20000 Then Set ws = Sheet2 ElseIf tbZipCode.Value < 60000 And tbZipCode.Value = 40000 Then Set ws = Sheet3 ElseIf tbZipCode.Value < 80000 And tbZipCode.Value = 60000 Then Set ws = Sheet4 ElseIf tbZipCode.Value < 100000 And 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 "JLGWhiz" wrote: See if this will work: 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 < 100000 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 "aintlifegrand79" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SMALL function to find X smallest number greater than Y | Excel Worksheet Functions | |||
| CountIf Greater Than/Find Greater Than | Excel Programming | |||
| Find minimum value greater than a particular value | Excel Worksheet Functions | |||
| find first value in a row with value greater than 0 | Excel Discussion (Misc queries) | |||
| Find Min of a row, but only greater than zero? | Excel Worksheet Functions | |||
Linear Mode
