NEED HELP ASAP using find function with a combo box

[aintlifegrand79]

I have a database that holds a bunch of information on my companies rep's. I
have created a userform (ufRepInfo) that fills text/check boxes with all the
relevant inforamtion about each particular rep based on the different zip
codes that the rep covers. My problem is that sometimes there can be more
than 1 rep per zip code depending on which market they work in. Therefore, I
need to make it so that my userform first checks for a certain zip code based
on what the user enters into the textbox (tbZipCode) and then checks for the
value selected in the combobox(cbMarkets) to see if the column that is
associated with 1 of the 5 different markets has a value in it. So lets say
that the 5 markets are A, B, C, D, E and I select the zip code 90210 and the
market C and click my command button (cbFindButton) what need to happen is
that my code needs to first check colomn 1 for value matching the 90210 then
it needs to look in column 19 to see if C has a value in it, and return the
rep information that corresponds with the row that has these to values in
them. One big issue I am having is that each of the 5 markets has it's own
column therefore in my mind the combobox needs to be able to check 5
different rows based on which of the 5 different markets is selected. I hope
this makes sense as I need get this finish within the next week. Here is my
code for just finding the rep info based on the Zip Code. Thank you for any
help you can give me, even if it is just a thought on how I might accomplish
this as it may spark an idea for me or someone esle.

Private Sub cbFindButton_Click()
'Find Rep Info
tbRepNumber.Value =
Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0, 1).Value
tbRepName.Value = Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0,
2).Value
tbRepAddress.Value =
Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0, 3).Value
tbRepState.Value =
Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0, 4).Value
tbRepZipCode.Value =
Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0, 5).Value
tbRepBusPhone.Value =
Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0, 6).Value
tbRepCellPhone.Value =
Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0, 7).Value
tbRepFax.Value = Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0,
8).Value
tbSAPNumber.Value =
Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0, 9).Value
tbRegionalManager.Value =
Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0, 10).Value
tbRMAddress.Value =
Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0, 11).Value
tbRMState.Value = Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0,
12).Value
tbRMZipCode.Value =
Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0, 13).Value
tbRMBusPhone.Value =
Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0, 14).Value
tbRMCellPhone.Value =
Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0, 15).Value
tbRMFax.Value = Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0,
16).Value
If Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0, 17).Value =
"x" Then cbIndustrialDrives = True
If Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0, 18).Value =
"x" Then cbMunicipalDrives = True
If Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0, 19).Value =
"x" Then cbHVAC = True
If Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0, 20).Value =
"x" Then cbElectricUtility = True
If Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0, 21).Value =
"x" Then cbOilGas = True
If Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0, 22).Value =
"x" Then cbMediumVoltage = True
If Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0, 23).Value =
"x" Then cbLowVoltage = True
If Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0, 24).Value =
"x" Then cbAfterMarket = True
tbInclusions.Value =
Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0, 25).Value
tbExclusions.Value =
Sheet1.Columns(1).Find(Val(tbZipCode.Value)).Offset(0, 26).Value
End Sub

 

[Joel]

Private Sub cbFindButton_Click()

With Sheet1
Select Case Market
Case "A"
MarketCol = 18
Case "B"
MarketCol = 19
Case "C"
MarketCol = 20
Case "D"
MarketCol = 21
Case "E"
MarketCol = 22
End Select

RowCount = 1
Do While .Range("A" & RowCount) <> ""
If (.Range("A" & RowCount) = Val(tbZipCode.Value)) And _
(.Cells(RowCount, MarketCol) <> "") 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
exit for
End If
RowCount = RowCount + 1
Loop
End With
End Sub

 

[Joel]

I left off a period on one line

from
Set Rep = Range("A" & RowCount)
to
Set Rep = .Range("A" & RowCount)

 

[aintlifegrand79]

Joel, thank you so much for your help. I get a errror when I run this that
looks like this
compile error:
Exit For not within For...Next
So I just got rid of the Exit For and it worked.
You are a god.

 

[Joel]

I change the loop type and forgot to change the EXIT..

from
exit for
to
exit do