Problem with if statement

  • Thread starter Thread starter aintlifegrand79
  • Start date Start date
A

aintlifegrand79

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
 
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
 
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....
 
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
 
I tried this but I get an error that says:
Compile Error:
Block If without End IF
 
This worked but only for the data on sheet 2. When I tried to find a zip
code >60000 I got nothing.
 
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.
 
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
 
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.
 
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.
 

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
finding data across multiple ws to populate a userform (Need Help) 6
Error help 1
NEED HELP ASAP using find function with a combo box 4
using find with a combo box 1
Combobox will not show list 4
creating a macro to find a cell value and return a statement in a 7

Back
Top