Macro with Vlookup but checking ranges of no fixed size

**bernborough** · 11-24-2010, 11:07 PM

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.

**mewingkitty** · 11-25-2010, 01:20 AM

kk I'm lost right off the bat.

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!.

kk the first four characters of cell contents Range A1:A10 of Sheet1 are

FORM
Some

race
Emai
SCST
Rail
Race
12.2

Sooooooooo..... HUH?

Is the races acronym every 10 lines from there on in, or is it SOMEWHERE in there every ten lines?

Just trying to find a good place to start from. When writing VBA for something like this it's easier to understand the whole thing from the get-go rather than write something specific for one step then start over again when you realize step 2 is based on step 1 and step 3 is based on step 2 etc.

Just looking for some confirmation on a good starting point.

Umm... Just a thought...
How bout we start somewhere else completely and break this down from massive piles of info in individual cells... into a normal-ish spreadsheet with information listed throughout in a meaningful fashion... It looks like you're importing this info as text from somewhere else, and it's badly in need of some organization....?

**bernborough** · 11-28-2010, 10:24 PM

Hi Mewingkitty,

I was really excited to see that you had thought about this a little. I’m afraid this information is only available to me in Text but I think that a few String functions will easily take case of what I need to do. Breaking down the all text into a traditional table would be overkill for what I need to do.

I can answer your questions, as follows:
- Step1: The first cell in the range A1:A10 that has its first 4 characters(incl Spaces) matching something in column of TrackCodes sheet is “SCST”, which can be found in TrackCodes!B402.
- Will there be another search for a Track Code within the next 10 lines? : No. Step 1 & 2 are once-only searches for the purpose of establishing a Rating for todays race meeting (ie. the VLookup would find SCST Rating = 4 in TrackCodes!). Steps 3 & 4 are then used to find out how many race entrants (for each race being run today) have competed at higher Ranked Tracks in the past.

Does this help?

My own thoughts are that the following formulas could be used at different stages of the Macro. You might know more elegant formulas. And I have very little idea how to structure VBA to identify those Ranges, Sub Ranges to conduct the data searches on.
Step 1&2 – Reference Rating=IF(ISERROR(VLOOKUP(LEFT(Sheet1!A1,4),TrackCodes!B:B,1,0)),0, VLOOKUP(LEFT(Sheet1!A1,4),TrackCodes!$B$2:$E$700,4,0))
Step 3 – Don’t know how to write this
Step 4a) – Offset(Find(LEFT(A1,6)=”Career”),1,0) and Offset(Find(TRUE=AND(ISNUMBER(LEFT(A1,1)),ISNUMBER(LEFT(A1,2)))),-1,0)
Step4b) – Matching Substring combining a Vlookup. No idea how to write this.