finding data across multiple ws to populate a userform (Need H
in [Programming]
|
From: aintlifegrand79 on 17 Jul 2008 16:11 When I do this joel I get the debugger. It highlights the line Set ws = Sheets("Sheet_") with the _ being whatever page corresponds to the zip code entered. Thank you for helping. "Joel" wrote: > If tbZipCode.Value < 20000 Then > Set ws = sheets("Sheet1") > ElseIf tbZipCode.Value < 40000 Then > Set ws = sheets("Sheet2") > ElseIf tbZipCode.Value < 60000 Then > Set ws = sheets("Sheet3") > ElseIf tbZipCode.Value < 80000 Then > Set ws = sheets("Sheet4") > ElseIf tbZipCode.Value >= 80000 Then > Set ws = sheets("Sheet5") > > > "aintlifegrand79" wrote: > > > My problem is that I have a code that searches for dat on multiple pages but > > has multiple entries that maybe the same under the first search condition > > therefore my code needs to check a secondary search condition but when my > > code tries to do this on some pages it doesn't work. So here is how it > > works, I have a userform (ufRepInfo) which has many text/check boxes on it > > that are populated from 5 different worksheets (Sheet1 (Zip Codes > > 00000-19999), Sheet2 (Zip Codes 20000-39999), Sheet3 (Zip Codes 40000-59999), > > Sheet4 (Zip Codes 60000-79999), Sheet5 (Zip Codes 80000-99999)). Also on the > > userform (ufRepInfo) is a textbox (tbZipCode), a combobox (cbMarket), and a > > commandbutton (cbFindButton). The goal is that when the user enters a zip > > code into (tbZipCode) then chooses 1 of 5 markets in (cbMarket) and clicks > > (cbFindButton) the code should first search for the zip code on the correct > > sheet, there might be multiple entries for one zip code, then it should > > search within those entries to find the entry for that zip code that has a > > "x" in the row that corresponds to the selected market (cbMarket). My code > > was working when I had just 2 worksheets but as I have gone along entering > > data I have found that I had about 3 entries per zip code and needed put the > > data on 5 worksheets. I have no problem populating the userform (ufRepInfo) > > if their is only 1 entry for a zip code but when their is multiple entries it > > doesn't populate. Hope this makes sense and thanks for your help. Here is > > my code: > > > > 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 >= 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
From: Joel on 17 Jul 2008 17:35 The sheet names need to be changed to match the names on the TAB at the bottom of each sheet. Th esheet names is the character string between the double quotes in the SET statement. "aintlifegrand79" wrote: > When I do this joel I get the debugger. It highlights the line > Set ws = Sheets("Sheet_") with the _ being whatever page corresponds to > the zip code entered. > > Thank you for helping. > > "Joel" wrote: > > > If tbZipCode.Value < 20000 Then > > Set ws = sheets("Sheet1") > > ElseIf tbZipCode.Value < 40000 Then > > Set ws = sheets("Sheet2") > > ElseIf tbZipCode.Value < 60000 Then > > Set ws = sheets("Sheet3") > > ElseIf tbZipCode.Value < 80000 Then > > Set ws = sheets("Sheet4") > > ElseIf tbZipCode.Value >= 80000 Then > > Set ws = sheets("Sheet5") > > > > > > "aintlifegrand79" wrote: > > > > > My problem is that I have a code that searches for dat on multiple pages but > > > has multiple entries that maybe the same under the first search condition > > > therefore my code needs to check a secondary search condition but when my > > > code tries to do this on some pages it doesn't work. So here is how it > > > works, I have a userform (ufRepInfo) which has many text/check boxes on it > > > that are populated from 5 different worksheets (Sheet1 (Zip Codes > > > 00000-19999), Sheet2 (Zip Codes 20000-39999), Sheet3 (Zip Codes 40000-59999), > > > Sheet4 (Zip Codes 60000-79999), Sheet5 (Zip Codes 80000-99999)). Also on the > > > userform (ufRepInfo) is a textbox (tbZipCode), a combobox (cbMarket), and a > > > commandbutton (cbFindButton). The goal is that when the user enters a zip > > > code into (tbZipCode) then chooses 1 of 5 markets in (cbMarket) and clicks > > > (cbFindButton) the code should first search for the zip code on the correct > > > sheet, there might be multiple entries for one zip code, then it should > > > search within those entries to find the entry for that zip code that has a > > > "x" in the row that corresponds to the selected market (cbMarket). My code > > > was working when I had just 2 worksheets but as I have gone along entering > > > data I have found that I had about 3 entries per zip code and needed put the > > > data on 5 worksheets. I have no problem populating the userform (ufRepInfo) > > > if their is only 1 entry for a zip code but when their is multiple entries it > > > doesn't populate. Hope this makes sense and thanks for your help. Here is > > > my code: > > > > > > 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 >= 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
From: aintlifegrand79 on 17 Jul 2008 18:42 Joel this works for all pages when I have only one entry for a zip code and for some zip codes were I have multiple entries. However, it doesn't work for all zip codes with multiple entries and it is not that one page works and others don't but that on some pages it won't populate the userform if the zip code has multiple entries and on some pages some of the zip codes with multiple entries will work and some won't. I can't figure out what is wrong, I have checked to make sure I have the right tab names in but it must be something in my code that is acting funky. "Joel" wrote: > The sheet names need to be changed to match the names on the TAB at the > bottom of each sheet. Th esheet names is the character string between the > double quotes in the SET statement. > > "aintlifegrand79" wrote: > > > When I do this joel I get the debugger. It highlights the line > > Set ws = Sheets("Sheet_") with the _ being whatever page corresponds to > > the zip code entered. > > > > Thank you for helping. > > > > "Joel" wrote: > > > > > If tbZipCode.Value < 20000 Then > > > Set ws = sheets("Sheet1") > > > ElseIf tbZipCode.Value < 40000 Then > > > Set ws = sheets("Sheet2") > > > ElseIf tbZipCode.Value < 60000 Then > > > Set ws = sheets("Sheet3") > > > ElseIf tbZipCode.Value < 80000 Then > > > Set ws = sheets("Sheet4") > > > ElseIf tbZipCode.Value >= 80000 Then > > > Set ws = sheets("Sheet5") > > > > > > > > > "aintlifegrand79" wrote: > > > > > > > My problem is that I have a code that searches for dat on multiple pages but > > > > has multiple entries that maybe the same under the first search condition > > > > therefore my code needs to check a secondary search condition but when my > > > > code tries to do this on some pages it doesn't work. So here is how it > > > > works, I have a userform (ufRepInfo) which has many text/check boxes on it > > > > that are populated from 5 different worksheets (Sheet1 (Zip Codes > > > > 00000-19999), Sheet2 (Zip Codes 20000-39999), Sheet3 (Zip Codes 40000-59999), > > > > Sheet4 (Zip Codes 60000-79999), Sheet5 (Zip Codes 80000-99999)). Also on the > > > > userform (ufRepInfo) is a textbox (tbZipCode), a combobox (cbMarket), and a > > > > commandbutton (cbFindButton). The goal is that when the user enters a zip > > > > code into (tbZipCode) then chooses 1 of 5 markets in (cbMarket) and clicks > > > > (cbFindButton) the code should first search for the zip code on the correct > > > > sheet, there might be multiple entries for one zip code, then it should > > > > search within those entries to find the entry for that zip code that has a > > > > "x" in the row that corresponds to the selected market (cbMarket). My code > > > > was working when I had just 2 worksheets but as I have gone along entering > > > > data I have found that I had about 3 entries per zip code and needed put the > > > > data on 5 worksheets. I have no problem populating the userform (ufRepInfo) > > > > if their is only 1 entry for a zip code but when their is multiple entries it > > > > doesn't populate. Hope this makes sense and thanks for your help. Here is > > > > my code: > > > > > > > > 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 >= 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
From: Joel on 17 Jul 2008 21:19 See code changes and comments below. Select Case tbZipCode.Value Case Is < 20000 Set ws = Sheet1 Case Is < 40000 Set ws = Sheet2 Case Is < 60000 Set ws = Sheet3 Case Is < 80000 Set ws = Sheet4 Case Is >= 80000 Set ws = Sheet5 End Select With ws Select Case cbMarket Case "Industrial Drives" cbMarketCol = "R" Case "Municipal Drives (W&E)" cbMarketCol = "S" Case "HVAC" cbMarketCol = "T" Case "Electric Utility" cbMarketCol = "U" Case "Oil and Gas" cbMarketCol = "V" '--------------------------------------------- ' where is W, X, and Y '--------------------------------------------- End Select RowCount = 1 Do While .Range("A" & RowCount) <> "" If .Range("A" & RowCount) = Val(tbZipCode.Value) And _ .Cells(RowCount, cbMarketCol) <> "" Then tbRepNumber.Value = .Range("B" & RowCount).Value tbRepName.Value = .Range("C" & RowCount).Value tbRepAddress.Value = .Range("D" & RowCount).Value tbRepState.Value = .Range("E" & RowCount).Value tbRepZipCode.Value = .Range("F" & RowCount).Value tbRepBusPhone.Value = .Range("G" & RowCount).Value tbRepCellPhone.Value = .Range("H" & RowCount).Value tbRepFax.Value = .Range("I" & RowCount).Value tbSAPNumber.Value = .Range("J" & RowCount).Value tbRegionalManager.Value = .Range("K" & RowCount).Value tbRMAddress.Value = .Range("L" & RowCount).Value tbRMState.Value = .Range("M" & RowCount).Value tbRMZipCode.Value = .Range("N" & RowCount).Value tbRMBusPhone.Value = .Range("O" & RowCount).Value tbRMCellPhone.Value = .Range("P" & RowCount).Value tbRMFax.Value = .Range("Q" & RowCount).Value If .Range("R" & RowCount).Value = "x" Then cbIndustrialDrives = True Else cbIndustrialDrives = False End If If .Range("S" & RowCount).Value = "x" Then cbMunicipalDrives = True Else cbMunicipalDrives = False End If If .Range("T" & RowCount).Value = "x" Then cbHVAC = True Else cbHVAC = False End If If .Range("U" & RowCount).Value = "x" Then cbElectricUtility = True Else cbElectricUtility = False End If If .Range("V" & RowCount).Value = "x" Then cbOilGas = True Else cbOilGas = False End If If .Range("W" & RowCount).Value = "x" Then cbMediumVoltage = True Else cbMediumVoltage = False End If If .Range("X" & RowCount).Value = "x" Then cbLowVoltage = True Else cbLowVoltage = False End If If .Range("Y" & RowCount).Value = "x" Then cbAfterMarket = True Else cbAfterMarket = False End If tbInclusions.Value = .Range("Z" & RowCount).Value tbExclusions.Value = .Range("AA" & RowCount).Value End If RowCount = RowCount + 1 Loop End With "aintlifegrand79" wrote: > Joel this works for all pages when I have only one entry for a zip code and > for some zip codes were I have multiple entries. However, it doesn't work > for all zip codes with multiple entries and it is not that one page works and > others don't but that on some pages it won't populate the userform if the zip > code has multiple entries and on some pages some of the zip codes with > multiple entries will work and some won't. I can't figure out what is wrong, > I have checked to make sure I have the right tab names in but it must be > something in my code that is acting funky. > > "Joel" wrote: > > > The sheet names need to be changed to match the names on the TAB at the > > bottom of each sheet. Th esheet names is the character string between the > > double quotes in the SET statement. > > > > "aintlifegrand79" wrote: > > > > > When I do this joel I get the debugger. It highlights the line > > > Set ws = Sheets("Sheet_") with the _ being whatever page corresponds to > > > the zip code entered. > > > > > > Thank you for helping. > > > > > > "Joel" wrote: > > > > > > > If tbZipCode.Value < 20000 Then > > > > Set ws = sheets("Sheet1") > > > > ElseIf tbZipCode.Value < 40000 Then > > > > Set ws = sheets("Sheet2") > > > > ElseIf tbZipCode.Value < 60000 Then > > > > Set ws = sheets("Sheet3") > > > > ElseIf tbZipCode.Value < 80000 Then > > > > Set ws = sheets("Sheet4") > > > > ElseIf tbZipCode.Value >= 80000 Then > > > > Set ws = sheets("Sheet5") > > > > > > > > > > > > "aintlifegrand79" wrote: > > > > > > > > > My problem is that I have a code that searches for dat on multiple pages but > > > > > has multiple entries that maybe the same under the first search condition > > > > > therefore my code needs to check a secondary search condition but when my > > > > > code tries to do this on some pages it doesn't work. So here is how it > > > > > works, I have a userform (ufRepInfo) which has many text/check boxes on it > > > > > that are populated from 5 different worksheets (Sheet1 (Zip Codes > > > > > 00000-19999), Sheet2 (Zip Codes 20000-39999), Sheet3 (Zip Codes 40000-59999), > > > > > Sheet4 (Zip Codes 60000-79999), Sheet5 (Zip Codes 80000-99999)). Also on the > > > > > userform (ufRepInfo) is a textbox (tbZipCode), a combobox (cbMarket), and a > > > > > commandbutton (cbFindButton). The goal is that when the user enters a zip > > > > > code into (tbZipCode) then chooses 1 of 5 markets in (cbMarket) and clicks > > > > > (cbFindButton) the code should first search for the zip code on the correct > > > > > sheet, there might be multiple entries for one zip code, then it should > > > > > search within those entries to find the entry for that zip code that has a > > > > > "x" in the row that corresponds to the selected market (cbMarket). My code > > > > > was working when I had just 2 worksheets but as I have gone along entering > > > > > data I have found that I had about 3 entries per zip code and needed put the > > > > > data on 5 worksheets. I have no problem populating the userform (ufRepInfo) > > > > > if their is only 1 entry for a zip code but when their is multiple entries it > > > > > doesn't populate. Hope this makes sense and thanks for your help. Here is > > > > > my code: > > > > > > > > > > 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 >= 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
From: aintlifegrand79 on 17 Jul 2008 21:50 Joel I think I have figured the problem out. When I have had multiple entries for a zip code it is really for a large range of zip codes. I have the row formatted to zip codes but when I dragged the series of zip codes it is not keeping the actual numbers as zip codes but as 5 digits with a demicmal place and numbers after it. Basically instead of being 90210 it is 90209.89997891. I guess I am going to try and figure a easy way to change all the problem zip codes. Thanks for your help "Joel" wrote: > See code changes and comments below. > > Select Case tbZipCode.Value > > Case Is < 20000 > Set ws = Sheet1 > Case Is < 40000 > Set ws = Sheet2 > Case Is < 60000 > Set ws = Sheet3 > Case Is < 80000 > Set ws = Sheet4 > Case Is >= 80000 > Set ws = Sheet5 > End Select > > With ws > > Select Case cbMarket > Case "Industrial Drives" > cbMarketCol = "R" > Case "Municipal Drives (W&E)" > cbMarketCol = "S" > Case "HVAC" > cbMarketCol = "T" > Case "Electric Utility" > cbMarketCol = "U" > Case "Oil and Gas" > cbMarketCol = "V" > '--------------------------------------------- > ' where is W, X, and Y > '--------------------------------------------- > > End Select > RowCount = 1 > Do While .Range("A" & RowCount) <> "" > If .Range("A" & RowCount) = Val(tbZipCode.Value) And _ > .Cells(RowCount, cbMarketCol) <> "" Then > > tbRepNumber.Value = .Range("B" & RowCount).Value > tbRepName.Value = .Range("C" & RowCount).Value > tbRepAddress.Value = .Range("D" & RowCount).Value > tbRepState.Value = .Range("E" & RowCount).Value > tbRepZipCode.Value = .Range("F" & RowCount).Value > tbRepBusPhone.Value = .Range("G" & RowCount).Value > tbRepCellPhone.Value = .Range("H" & RowCount).Value > tbRepFax.Value = .Range("I" & RowCount).Value > tbSAPNumber.Value = .Range("J" & RowCount).Value > tbRegionalManager.Value = .Range("K" & RowCount).Value > tbRMAddress.Value = .Range("L" & RowCount).Value > tbRMState.Value = .Range("M" & RowCount).Value > tbRMZipCode.Value = .Range("N" & RowCount).Value > tbRMBusPhone.Value = .Range("O" & RowCount).Value > tbRMCellPhone.Value = .Range("P" & RowCount).Value > tbRMFax.Value = .Range("Q" & RowCount).Value > If .Range("R" & RowCount).Value = "x" Then > cbIndustrialDrives = True > Else > cbIndustrialDrives = False > End If > If .Range("S" & RowCount).Value = "x" Then > cbMunicipalDrives = True > Else > cbMunicipalDrives = False > End If > If .Range("T" & RowCount).Value = "x" Then > cbHVAC = True > Else > cbHVAC = False > End If > If .Range("U" & RowCount).Value = "x" Then > cbElectricUtility = True > Else > cbElectricUtility = False > End If > If .Range("V" & RowCount).Value = "x" Then > cbOilGas = True > Else > cbOilGas = False > End If > If .Range("W" & RowCount).Value = "x" Then > cbMediumVoltage = True > Else > cbMediumVoltage = False > End If > If .Range("X" & RowCount).Value = "x" Then > cbLowVoltage = True > Else > cbLowVoltage = False > End If > If .Range("Y" & RowCount).Value = "x" Then > cbAfterMarket = True > Else > cbAfterMarket = False > End If > tbInclusions.Value = .Range("Z" & RowCount).Value > tbExclusions.Value = .Range("AA" & RowCount).Value > End If > RowCount = RowCount + 1 > Loop > End With > > "aintlifegrand79" wrote: > > > Joel this works for all pages when I have only one entry for a zip code and > > for some zip codes were I have multiple entries. However, it doesn't work > > for all zip codes with multiple entries and it is not that one page works and > > others don't but that on some pages it won't populate the userform if the zip > > code has multiple entries and on some pages some of the zip codes with > > multiple entries will work and some won't. I can't figure out what is wrong, > > I have checked to make sure I have the right tab names in but it must be > > something in my code that is acting funky. > > > > "Joel" wrote: > > > > > The sheet names need to be changed to match the names on the TAB at the > > > bottom of each sheet. Th esheet names is the character string between the > > > double quotes in the SET statement. > > > > > > "aintlifegrand79" wrote: > > > > > > > When I do this joel I get the debugger. It highlights the line > > > > Set ws = Sheets("Sheet_") with the _ being whatever page corresponds to > > > > the zip code entered. > > > > > > > > Thank you for helping. > > > > > > > > "Joel" wrote: > > > > > > > > > If tbZipCode.Value < 20000 Then > > > > > Set ws = sheets("Sheet1") > > > > > ElseIf tbZipCode.Value < 40000 Then > > > > > Set ws = sheets("Sheet2") > > > > > ElseIf tbZipCode.Value < 60000 Then > > > > > Set ws = sheets("Sheet3") > > > > > ElseIf tbZipCode.Value < 80000 Then > > > > > Set ws = sheets("Sheet4") > > > > > ElseIf tbZipCode.Value >= 80000 Then > > > > > Set ws = sheets("Sheet5") > > > > > > > > > > > > > > > "aintlifegrand79" wrote: > > > > > > > > > > > My problem is that I have a code that searches for dat on multiple pages but > > > > > > has multiple entries that maybe the same under the first search condition > > > > > > therefore my code needs to check a secondary search condition but when my > > > > > > code tries to do this on some pages it doesn't work. So here is how it > > > > > > works, I have a userform (ufRepInfo) which has many text/check boxes on it > > > > > > that are populated from 5 different worksheets (Sheet1 (Zip Codes > > > > > > 00000-19999), Sheet2 (Zip Codes 20000-39999), Sheet3 (Zip Codes 40000-59999), > > > > > > Sheet4 (Zip Codes 60000-79999), Sheet5 (Zip Codes 80000-99999)). Also on the > > > > > > userform (ufRepInfo) is a textbox (tbZipCode), a combobox (cbMarket), and a > > > > > > commandbutton (cbFindButton). The goal is that when the user enters a zip > > > > > > code into (tbZipCode) then chooses 1 of 5 markets in (cbMarket) and clicks > > > > > > (cbFindButton) the code should first search for the zip code on the correct > > > > > > sheet, there might be multiple entries for one zip code, then it should > > > > > > search within those entries to find the entry for that zip code that has a > > > > > > "x" in the row that corresponds to the selected market (cbMarket). My code > > > > > > was working when I had just 2 worksheets but as I have gone along entering > > > > > > data I have found that I had about 3 entries per zip code and needed put the > > > > > > data on 5 worksheets. I have no problem populating the userform (ufRepInfo) > > > > > > if their is only 1 entry for a zip code but when their is multiple entries it > > > > > > doesn't populate. Hope this makes sense and thanks for your help. Here is > > > > > > my code: > > > > > > > > > > > > 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 >= 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
|
Pages: 1 Prev: get the name of the currently active procedure? Next: Code interrupted |