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

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

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

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

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

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