find how many times a word appears in a string at certain positions within the string

Hi everyone,

I have strings that are concatenated with ~ characters. an example of the format of the sorts of strings im working with is here:

(total rows,workbook name,worksheet name,column, top row, bottom row, row 1 data, row 2 data, ... , row total rows-1 data)
myString = "14~ThisWorkbook.Name~Sheets(1).Name~E~11~25~data0~data1~data2~data3~data4~data5~data6~data7~data8~data9~data10~data11~data12~data13"

I have kindly been provided with the following code by AlphaFrog which allows me to search this string for a given term and it will count the number of ~ characters to the left of the term.

code provided by AlphaFrog

Please Login or Register  to view this content.

How could I look at this string and count the number of times my term appears only it at certain positions (that is with a certain number of ~ characters to its left).
The possible positions the term can appear should be restricted to index number 6,13,20,27,..
by index number i mean the number of ~ characters in front of the term in myString

so from the above example
myString index(0) = 14
myString.index(1) = ThisWorkbook.Name
myString.index(2) = Sheets(1).Name
Etc

The index values to search for should be restricted to those mentioned above.. .. 6,13,20,27,..

Kind Regards
Jordan

Hello Jordan,

It would help to have more examples to work with and to see what the results should look like. Can you post a copy of the workbook?

Thanks for your reply Leith,

I've attached a sample file for you.

Please note the string examples i've provided in the WB are only to demonstrate how the strings are put together from the data

Regards
Jordan

Hello Jordan2322,

Thanks for posting the workbook. I would have been completely lost without the examples and explanations.

Do the numbers 6,13,20,27, etc. indicate the position of the tilde (~) character in the string?

Hello Jordan,

So everything before the 6th instance of the tilde is to be ignored. Where is the keyword to search for located? Is it in columns L and K?

Hello Jordan,


If you have a blank row separating your cell blocks it would simplify the code. Rows 3 to 10 is a count of 8 rows (inclusive) and 11 to 25 is a count of 15 (inclusive). Are you using 7 rows or 8? If 8 is not being used keeping it blank will make defining a cell block much easier by using the CurrentRegion property. Here is the code I have so far to find the terms and assign the cell block color. This macro is not complete.

Please Login or Register  to view this content.