Greater than less than in a find function

A

aintlifegrand79

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
 
B

Bernard Liengme

The first two sentences of your question suggest the solution is Access
best wishes
 
J

JLGWhiz

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
 
A

aintlifegrand79

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.
 
A

aintlifegrand79

If it would make it any easier I could probably do with only 3 worksheets but
I would prefer to use 5 worksheets.
 
A

aintlifegrand79

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with find function 2
help with error 1
Problem with if statement 12
finding data across multiple ws to populate a userform (Need Help) 6
Error help 1
VBA Coding Help for Beginner 0
Word macro that can call cells out of Excel for a find and replace 0
Textbox input goes to 2 sheets in same wbk 0

Top