Problem with if statement

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
 
G

Geoff

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
 
C

Corey ....

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

Don Guillett

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
 
A

aintlifegrand79

I tried this but I get an error that says:
Compile Error:
Block If without End IF
 
A

aintlifegrand79

This worked but only for the data on sheet 2. When I tried to find a zip
code >60000 I got nothing.
 
A

aintlifegrand79

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

Rick Rothstein \(MVP - VB\)

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
 
A

aintlifegrand79

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

Don Guillett

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
Greater than less than in a find function 5
finding data across multiple ws to populate a userform (Need Help) 6
Error help 1
VBA Coding Help for Beginner 0
NEED HELP ASAP using find function with a combo box 4
Textbox input goes to 2 sheets in same wbk 0

Top