VBA to Count Occurances of Value Within String

[DuncR6]

What I want:
As outlined below....."does the exported receiver number (unit+number) occur anywhere in the generated string of supposedly missing receivers? If it does then count how many in that particular gap occur and reduce the "missing" quantity by that value to get a true missing figure."


Background:
I have a file that tests a receiving system for missing receivers. The receiver number is a unique number by plant not company wide. Therefore the inspected number has to be "Unit+Receiver." When a gap is detected in the sequence it flags the count of that gap as missing receivers at the unit location.

The problem is that the receivers might not actually be missing. The way it needs to be sorted causes issues and might falsely accuse a receiver of being missing. Therefore I need a method to "force load" receivers into the data to reduce the count of missing receivers down if the receiver number occurs at anywhere in the data set.

I'm able to get a string generated of "missing" receivers that I need to focus on. The key is that I need to be able to say "does the exported receiver number (unit+number) occur anywhere in the generated string of supposedly missing receivers? If it does then count how many in that particular gap occur and reduce the "missing" quantity by that value to get a true missing figure." The rest are just do to accounting/sorting issues and are not of concern.

Attached is a sample of the data I'm working with. I also included the macros that generate the formulas and the string of gapped numbers to help out.

My excel file via Google Docs <- The highlighted green cells in this file are manually inserted to simulate the problem in order to build the solution.

The macro "CheckForValues" is what I was playing around with until I realized that I need a count from the string that occur in the "combined" column. That's where I became stuck and hopefully someone smarter than me can help. I don't care if this is done via VBA or a formula if that matters. Thanks!

 

[solitude]

First thing, even for someone who has just joined this forum thats a lot of text in your post. Very discouraging to strangers on internet!

Secondly, I'm not 100% sure that this what you need but have you tried 'split' function in vba? Takes two parameters - first one is some string/value in a range etc and second parameter is a delimiter (could be "," in your case). And this returns an array/variant with string split at the delimiters.

dim x
x = Split(Range("A1").Value, ",")

Now, you can loop through this array and do all sorts of counting.

Let me know if this helps.

 

[DuncR6]

I do apologize for the wall of text. I was trying to be complete in the first post so the thread didn't spiral out with confusion.

I have not used the split function before, so that's something I'll go play around with and see what happens.