Macro with Vlookup but checking ranges of no fixed size

Hello,

I am trying to do the following but I've realised only VBA can do this. Look for help to code.

What I am trying to do is, for each race, count the number of race partipants that have a history of having competed at a Track Code that is superior to the Track Code that they are competing at today. But exclude any "Scratched" runners (explained below) from the count.

Step 1 - Assess the first four characters (incl spaces) of cell contents in Range A1:A10 of Sheet1! and find the first set of four characters that match with a value in columnB of TrackCodes!.

Step 2 - Using that value from Step 1 (in this example, it would be "SCST"), run a Vlookup to check column E of TrackCodes! and establish what can be called a Reference Rating.

Step 3 - For each data range beginning from just below the first cell in columnA whose first five characters are "RACE[space]" and ending just above the next instance of "Race[space]", in this case the range is A11:A110, COUNT the number of CASES identified according to Step 4.

Step 4
a) Each Case is a Search conducted inside a Sub Range of Step 3 found in column A just below cells beginning with the string "Career" and ending just above the next cell down that contains only One or Two Numeric Characters (First Sub Range is A17:A21, Last Sub Range is Range would be A100:A116)

b) For each range identified from (a) & (b), search the first 20 characters (incl spaces) of each cell for the presence of four characters in a row (incl spaces) that matches a value in columnB of TrackCodes!

c) For each match, conduct a Vlookup to check columnE in TrackCodes! for a Rating.

d) If the Rating number is at least 1 less than the Reference Rating established in Step2, this is a CASE that should be counted. BUT, if the cell found using Offset(First cell of Sub Range,-3,3) equals "Scratched", do not COUNT anything found from this Sub Range.

e) Once a case if found in (d) move on to the next Sub Range (described in (a)) and repeat steps (b) to (d) for all Sub Ranges (Last Sub Range is A100:A116). Populate the COUNT result in the first blank cell in Sheet3!columnA.

f) Repeat steps (a) to (b) on the next Step 3 Range (ie. the next race).

I think it sounds more complicated than it is and I would be very happy to have someone show me that. I would look forward to learning the structure of some of the String queries involved. Very grateful for anyone's help on this.