VB Search Function Problem...Help!

davegow

New Member
Joined
Apr 5, 2009
Messages
1
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,723
I'm guessing. Good luck. Dave
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,320
Messages
5,600,938
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top