jeudi 28 janvier 2016

Intelligent VBA code from exhausting Excel formula

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