VB Search Function Problem...Help!

[davegow]

I am attempting to perform a search on a data sheet containing multiple columns of data.

The policy number input into PolicySearch_TB should search for a match on the input criteria, under columns:-

... K (PolicyNumber1_TB)
... AE (PolicyNumber2_TB)
... AY (PolicyNumber3_TB)
... BS (PolicyNumber4_TB)

If there is a match, the row from the data sheet should retrieve all data items to their relevant box.

I can get an individual match to work if I search only 1 column, but not a range of 4 columns. Please can anyone help??

Here is my code
-----------------------------------------------------------------------
Private Sub Policy_CMD_Click()
ActiveWorkbook.Save
If PolicySearch_TB.Value = "" Then
MsgBox "Please enter Policy Number to search", vbCritical, "Alert"
Exit Sub
End If
Dim Search As Boolean
Search = False
Dim PolicySearch As String
Sheets("Data").Select
For mm = 5 To 5000
PolicySearch_TB = PolicyNumber1_TB.Value
If Range("K" & mm).Value = PolicyNumber1_TB Then
Search = True
Else
PolicySearch_TB = PolicyNumber2_TB.Value
If Range("AE" & mm).Value = PolicyNumber2_TB Then
Search = True
PolicySearch_TB = PolicyNumber3_TB.Value
Else
If Range("AY" & mm).Value = PolicyNumber3_TB Then
Search = True
PolicySearch_TB = PolicyNumber4_TB.Value
Else
If Range("BS" & mm).Value = PolicyNumber4_TB Then
Search = True
End If

If Search = True Then
DateLogged_TB = Range("A" & mm).Value
TimeLogged_TB = Range("B" & mm).Value
Planholder_TB = Range("C" & mm).Value
TotalPols_TB = Range("D" & mm).Value
RelatedPols_TB = Range("E" & mm).Value
AdviserName_TB = Range("F" & mm).Value
AdviserFirm_TB = Range("G" & mm).Value
RSU_CB = Range("H" & mm).Value
SalesSupport_CB = Range("I" & mm).Value
ProcessorName_CB = Range("J" & mm).Value
PolicyNumber1_TB = Range("K" & mm).Value
PlanType1_CB = Range("L" & mm).Value
TransferType1_CB = Range("M" & mm).Value
TransferCompany1_TB = Range("N" & mm).Value
InvestmentAmount1_TB = Range("O" & mm).Value
SIPP1_CB = Range("P" & mm).Value
FRPComments1_TB = Range("Q" & mm).Value
HighLevelCause1A_CB = Range("R" & mm).Value
Resolved1A_CB = Range("S" & mm).Value
HighLevelCause2A_CB = Range("T" & mm).Value
Resolved1B_CB = Range("U" & mm).Value
HighLevelCause3A_CB = Range("V" & mm).Value
Resolved1C_CB = Range("W" & mm).Value
HighLevelCause4A_CB = Range("X" & mm).Value
Resolved1D_CB = Range("Y" & mm).Value
HighLevelCause5A_CB = Range("Z" & mm).Value
Resolved1E_CB = Range("AA" & mm).Value
LastChase1_TB = Range("AB" & mm).Value
NextChase1_TB = Range("AC" & mm).Value
InForce1_CB = Range("AD" & mm).Value
PolicyNumber2_TB = Range("AE" & mm).Value
PlanType2_CB = Range("AF" & mm).Value
TransferType2_CB = Range("AG" & mm).Value
TransferCompany2_TB = Range("AH" & mm).Value
InvestmentAmount2_TB = Range("AI" & mm).Value
SIPP2_CB = Range("AJ" & mm).Value
FRPComments2_TB = Range("AK" & mm).Value
HighLevelCause1B_CB = Range("AL" & mm).Value
Resolved2A_CB = Range("AM" & mm).Value
HighLevelCause2B_CB = Range("AN" & mm).Value
Resolved2B_CB = Range("AO" & mm).Value
HighLevelCause3B_CB = Range("AP" & mm).Value
Resolved2C_CB = Range("AQ" & mm).Value
HighLevelCause4B_CB = Range("AR" & mm).Value
Resolved2D_CB = Range("AS" & mm).Value
HighLevelCause5B_CB = Range("AT" & mm).Value
Resolved2E_CB = Range("AU" & mm).Value
LastChase2_TB = Range("AV" & mm).Value
NextChase2_TB = Range("AW" & mm).Value
InForce2_CB = Range("AX" & mm).Value
PolicyNumber3_TB = Range("AY" & mm).Value
PlanType3_CB = Range("AZ" & mm).Value
TransferType3_CB = Range("BA" & mm).Value
TransferCompany3_TB = Range("BB" & mm).Value
InvestmentAmount3_TB = Range("BC" & mm).Value
SIPP3_CB = Range("BD" & mm).Value
FRPComments3_TB = Range("BE" & mm).Value
HighLevelCause1C_CB = Range("BF" & mm).Value
Resolved3A_CB = Range("BG" & mm).Value
HighLevelCause2C_CB = Range("BH" & mm).Value
Resolved3B_CB = Range("BI" & mm).Value
HighLevelCause3C_CB = Range("BJ" & mm).Value
Resolved3C_CB = Range("BK" & mm).Value
HighLevelCause4C_CB = Range("BL" & mm).Value
Resolved3D_CB = Range("BM" & mm).Value
HighLevelCause5C_CB = Range("BN" & mm).Value
Resolved3E_CB = Range("BO" & mm).Value
LastChase3_TB = Range("BP" & mm).Value
NextChase3_TB = Range("BQ" & mm).Value
InForce3_CB = Range("BR" & mm).Value
PolicyNumber4_TB = Range("BS" & mm).Value
PlanType4_CB = Range("BT" & mm).Value
TransferType4_CB = Range("BU" & mm).Value
TransferCompany4_TB = Range("BV" & mm).Value
InvestmentAmount4_TB = Range("BW" & mm).Value
SIPP4_CB = Range("BX" & mm).Value
FRPComments4_TB = Range("BY" & mm).Value
HighLevelCause1D_CB = Range("BZ" & mm).Value
Resolved4A_CB = Range("CA" & mm).Value
HighLevelCause2D_CB = Range("CB" & mm).Value
Resolved4B_CB = Range("CC" & mm).Value
HighLevelCause3D_CB = Range("CD" & mm).Value
Resolved4C_CB = Range("CE" & mm).Value
HighLevelCause4D_CB = Range("CF" & mm).Value
Resolved4D_CB = Range("CG" & mm).Value
HighLevelCause5D_CB = Range("CH" & mm).Value
Resolved4E_CB = Range("CI" & mm).Value
LastChase4_TB = Range("CJ" & mm).Value
NextChase4_TB = Range("CK" & mm).Value
InForce4_CB = Range("CL" & mm).Value
Log_CMD.Enabled = False
Client_CMD.Enabled = False
Policy_CMD.Enabled = False
Update_CMD.Enabled = True
End If

If Search = False Then
MsgBox "The Policy Number you have entered cannot be found", vbCritical, "Alert"
End If
End If
End If
End If
Next mm
End Sub

 

[NdNoviceHlp]

I'm guessing. Good luck. Dave

For mm = 5 To 5000
If Sheets("Data").Range("K" & mm).Value = PolicySearch_TB.Value Or _
Sheets("Data").Range("AE" & mm).Value = PolicySearch_TB.Value Or _
Sheets("Data").Range("BS" & mm).Value = PolicySearch_TB.Value Then
Search = True
Exit For
Next mm
If Search Then
DateLogged_TB = Sheets("Data").Range("A" & mm).Value
TimeLogged_TB = Sheets("Data").Range("B" & mm).Value
'etc
'etc
Policy_CMD.Enabled = False
Update_CMD.Enabled = True
Else
MsgBox "The Policy Number you have entered cannot be found", vbCritical, "Alert"
End If