VBA -Distance between 2 strings

I have written a simple macro to parse an e-mail that contains an HTML table. The table includes Brands in the first column, Countries in the second column, and a set of metrics in the following 6 columns. Based on the occurrence of a particular brand or country name in that e-mail, the macro performs various tasks.

This all works perfectly for the simple case, e.g.,

If InStr(strMsg, "Country1") > 0 Then <do xyz>

The script easily finds mentions of "Country1" in the table and performs the rest of the code as necessary.

Now I'm trying to implement for a situation where a row of this table contains "Brand1" in the first column AND "Country1" in the second column.

If InStr(strMsg, "Country1") > 0 And InStr(strMsg, "Brand1") > 0 Then 
 If (InStr(strMsg, "Country1") - InStr(strMsg, "Brand1") < 600) Then <do xyz>

The idea is (1) to check for the existence of both strings and then (2) use the character distance between them to confirm that they're in the same row of the table. This capitalizes on the fact the distance of the 2 strings, when they're in adjacent columns on the same row, is much smaller than the distance when they're in separate rows.

For some reason, the script always seems to execute when it finds both strings, regardless of which row they're on.

It's possible my distance (600) is too high, but there are well over 600 characters (incl. HTML mark-up) when the strings occur in different rows of the table.

• Is the code, theoretically, correct?


• When coming up with my 'distance,' should I be excluding certain special characters or mark-up?


• Any better ideas?

Thanks in advance.