Intelligent VBA code from exhausting Excel formula -

i have working formula searches 1 sheet sub-string, , if finds sub-string, returns mid function, if not, moves next cell down , searches again. however, formula created not right way this, hard coded, , cannot compensate more entries without addition of yet if statement. cannot change these relative cell references, many of cells should return same value (dates of event, multiple item #'s occurring on same date), why routine needs run through each , every record (currently a1-a6, grow dynamically reports come in) until finds exact match.

i convert formula "smart" vba subroutine looks in next cell down if sub-string not found in first cell searched. allow dynamic , run through many entries have while returning correct result.

here 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 sample of formula working correctly, returning correct risk claim # each item id (in first row 10045597). can see, many item id's return same risk claim #, linked 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 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 *******/erich ******* location phone #: * claim details (what happened):    fraudulently rented under customers account , never returned. location/address 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 above mentioned equipment #(s)? – y/n: n bulk type: bulk cost: police agency theft reported to: abbotsford pd police agency phone #: * police report/case number: 16-**34 email intended recipient only.  if not intended recipient please disregard, , not use information purpose