Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find mid value of string data in data with integers
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find mid value of string data in data with integers
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
find mid value of string data in data with integers
Why 65 to 71?
I also did not understand the line "If Range(Chr(x) & i).Text = v Then" llama wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using excel to pick data from a list, reporting names and integers | Excel Discussion (Misc queries) | |||
Macro to find text string in a column and paste data in another | Excel Discussion (Misc queries) | |||
Extracting integers from a text string. | Excel Worksheet Functions | |||
Find Data in a String | Excel Programming | |||
Wish to parse through a text string to find data | Excel Programming |