find mid value of string data in data with integers

[Maxi]

1,2,3,4,5,6,7
2,3,4,5,6,7,8
1,2,3,6,7,8,9
1,4,5,6,7,8,9
1,2,3,5,6,7,8

I have the above data in range A1:G5

In my VBA code I have a string variable which has a value "1, 2, 3, 4"

I have to search each number in this string variable in the range
A1:G1, A2:G2, A3:G3, A4:G4 and A5:G5 and if 3 or more number matches
then increment value of freq variable by 1.

Example:
freq variable has a value 0
In the above string:
1 2 3 4 all numbers matches in row 1 which is >=3 hence freq variable
will have value 1
2 3 4 numbers matches in row 2 => freq variable = 2
1 2 3 numbers matches in row 3 => freq variable = 3
1 4 numbers matches in row 4 (which is less than 3 hence no increment)
=> freq variable = 3
1 2 3 numbers mathes in row 5 => freq variable = 4

freq = 4

If the string variable value is "1, 2, 5, 7" then freq should be 5

what is the best approach/logic to find the correct answer. How would I
put it in a vba code?

 

[llama]

Maxi

This should solve your problem, strValString is your string of numbers
("1,2,3,4")

Function FindFreq(strValString As String)

Dim iFreq As Integer
Dim iCount As Integer

For i = 1 To 5

For x = 65 To 71

For Each v In Split(strValString, ",")

If Range(Chr(x) & i).Text = v Then
iCount = iCount + 1
End If

Next
Next

If iCount > 2 Then
iFreq = iFreq + 1
End If

iCount = 0

Next

Debug.Print iFreq


End Function

Paul

 

[Maxi]

Why 65 to 71?

I also did not understand the line "If Range(Chr(x) & i).Text = v Then"