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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
3,024
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,681
Messages
5,838,776
Members
430,568
Latest member
bortey

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