I have a working formula that searches one sheet for a sub-string, and if it finds that sub-string, returns a mid function, if not, it moves to the next cell down and searches again. However, the formula I created is obviously not the right way to do this, as it is hard coded, and cannot compensate for more entries without my addition of yet another if statement. I cannot change these to relative cell references, as many of the cells should return the same value (dates of an event, with multiple item #'s occurring on the same date), which is why the routine needs to run through each and every record (currently A1-A6, but will grow dynamically as reports come in) until it finds an exact match.
I would like to convert this formula to a "smart" VBA subroutine that just looks in the next cell down if the sub-string was not found in the first cell searched. This would allow it to be dynamic and run through however many entries I have while returning the correct result.
Here is my current excel formula:
=IF(ISNUMBER(SEARCH(B3,'\\Vae1vwinfhomp1\home\1625\I58339\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$1))=TRUE,IFERROR(MID('H:\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$1,FIND("RISK CLAIM",'H:\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$1),(FIND("DATE OF LOSS",'H:\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$1)-(FIND("RISK CLAIM",'H:\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$1)+5))),""),IF(ISNUMBER(SEARCH(B3,'\\Vae1vwinfhomp1\home\1625\I58339\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$2))=TRUE,IFERROR(MID('H:\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$2,FIND("RISK CLAIM",'H:\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$2),(FIND("DATE OF LOSS",'H:\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$2)-(FIND("RISK CLAIM",'H:\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$2)+5))),""),IF(ISNUMBER(SEARCH(B3,'\\Vae1vwinfhomp1\home\1625\I58339\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$3))=TRUE,IFERROR(MID('H:\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$3,FIND("RISK CLAIM",'H:\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$3),(FIND("DATE OF LOSS",'H:\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$3)-(FIND("RISK CLAIM",'H:\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$3)+5))),""),IF(ISNUMBER(SEARCH(B3,'\\Vae1vwinfhomp1\home\1625\I58339\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$4))=TRUE,IFERROR(MID('H:\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$4,FIND("RISK CLAIM",'H:\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A4),(FIND("DATE OF LOSS",'H:\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$4)-(FIND("RISK CLAIM",'H:\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$4)+5))),""),IF(ISNUMBER(SEARCH(B3,'\\Vae1vwinfhomp1\home\1625\I58339\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$5))=TRUE,IFERROR(MID('H:\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$5,FIND("RISK CLAIM",'H:\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$5),(FIND("DATE OF LOSS",'H:\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$5)-(FIND("RISK CLAIM",'H:\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$5)+5))),""),IF(ISNUMBER(SEARCH(B3,'\\Vae1vwinfhomp1\home\1625\I58339\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$6))=TRUE,IFERROR(MID('H:\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$6,FIND("RISK CLAIM",'H:\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$6),(FIND("DATE OF LOSS",'H:\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$6)-(FIND("RISK CLAIM",'H:\Document\[Risk_Excel_Export.xlsx]Sheet1'!$A$6)+5))),""),0))))))
Here is a sample of the formula working correctly, returning the correct RISK CLAIM # for each Item ID (in the first row that would be 10045597). As you can see, many Item ID's will return the same RISK CLAIM #, as they are linked to the same claim #.
10045597 SERIAL/VIN #: 1984 MAKE: KENT MODEL: KF 4 SS YR: 2012 TYPE OF EQUIPMENT: SKID STEER/MINI EXCAVATOR BREAKER ORIGINAL EQUIPMENT COST: 3832.71 RISK CLAIM #: RPP3535TF
10251995 SERIAL/VIN #: 177734255 MAKE: STIHL MODEL: TS420-14 YR: 2015 TYPE OF EQUIPMENT: CUT OFF SAW ORIGINAL EQUIPMENT COST: 730.00 RISK CLAIM #: RPP3534TF
10353520 SERIAL/VIN #: 007379 MAKE: DEWALT MODEL: D25980K YR: 2015 TYPE OF EQUIPMENT: DEMO HAMMER ORIGINAL EQUIPMENT COST: 1118.78 RISK CLAIM #: RPP3534TF
10326567 SERIAL/VIN #: 71248 MAKE: HILTI MODEL: TE60-ATC YR: 2015 TYPE OF EQUIPMENT: ROTARY HAMMER ORIGINAL EQUIPMENT COST: 1115.49 RISK CLAIM #: RPP3534TF
10335480 SERIAL/VIN #: 179146608 MAKE: STIHL MODEL: TS420 YR: 2015 TYPE OF EQUIPMENT: CUT OFF SAW ORIGINAL EQUIPMENT COST: 824.96 RISK CLAIM #: RPP3534TF
10331620 SERIAL/VIN #: 006159 MAKE: DEWALT MODEL: D25980K YR: 2014 TYPE OF EQUIPMENT: DEMO HAMMER ORIGINAL EQUIPMENT COST: 1117.42 RISK CLAIM #: RPP3534TF
10189822 SERIAL/VIN #: 7305316 MAKE: MULTIQUIP MODEL: DCA70SSJU4I YR: 2013 TYPE OF EQUIPMENT: GENERATOR ORIGINAL EQUIPMENT COST: 33068.65 RISK CLAIM #: RPP3520T
1226605 SERIAL/VIN #: 5653875 MAKE: MULTIQUIP MODEL: GAW180HE1 YR: 2011 TYPE OF EQUIPMENT: WELDER ORIGINAL EQUIPMENT COST: 2442.03 RISK CLAIM #: RPP3491T
1219041 SERIAL/VIN #: 20036780 MAKE: WACKER MODEL: BS 60-2I YR: 2011 TYPE OF EQUIPMENT: RAMMER ORIGINAL EQUIPMENT COST: 2642.09 RISK CLAIM #: RPP3490T
10391557 SERIAL/VIN #: 30101214 MAKE: WACKER MODEL: WP1550AW YR: 2015 TYPE OF EQUIPMENT: VIB PLATE ORIGINAL EQUIPMENT COST: 1499.52 RISK CLAIM #: RPP3439TF
10305672 SERIAL/VIN #: 4KNTT1210FL160572 MAKE: TOW MASTER MODEL: T-5DT YR: 2014 Lic. Plate : MO / 63E0HL RISK CLAIM #: RPP3439TF
Sample of text that is being searched
UNITED RENTALS – RPP CLAIM – STOLEN EQUIPMENT RISK CLAIM #: RPP3535TF DATE OF LOSS: 1/15/16 REPORT DATE: 1/19/16 LOCATION CODE: 481 LOSS STATE: BC - CANADA CONTACT PERSON(s): JUSTIN TETRAULT/ERICH THIESSEN LOCATION PHONE #: 604-792-0034 CLAIM DETAILS (what happened): FRAUDULENTLY RENTED UNDER ANOTHER CUSTOMERS ACCOUNT AND WAS NEVER RETURNED. LOCATION/ADDRESS WHERE THEFT OCCURRED: LOC 481 UNITED RENTALS EQUIP#(s) & DESCRIPTION: EQ # - 10045597 YR: 2012 MAKE: KENT MODEL: KF 4 SS SERIAL/VIN #: 1984 TYPE OF EQUIPMENT: SKID STEER/MINI EXCAVATOR BREAKER ORIGINAL EQUIPMENT COST: 3832.71 BULK EQUIPMENT (non-serialized equipment/accessories) STOLEN ALONG WITH THE ABOVE MENTIONED EQUIPMENT #(s)? – Y/N: N BULK TYPE: BULK COST: POLICE AGENCY THEFT REPORTED TO: ABBOTSFORD PD POLICE AGENCY PHONE #: 604-859-5225 POLICE REPORT/CASE NUMBER: 16-2034 This email is intended for the recipient only. If you are not the intended recipient please disregard, and do not use the information for any purpose
Aucun commentaire:
Enregistrer un commentaire