Find Exact Value within a string

[vladi305]

I have a function looking for a range of 5 columns and one row searching within a range of 5 columns and multiple rows as the pic attached.

The values on the right side are the one that need to be check if they match any of the value on the left side
one row at a time will be search through all the rows on the left side

So my function is giving me errors writing values on the G column
Also maybe there's a better way of writing this

I want to just write on column G if there's a value found either true or how many did it find 2 or 3 values or 4

Function CheckIfAnyWon2(Arg1 As Range, Arg12 As Range) As Double

Dim iCell As Range
Dim varVal As String
Dim varVal2 As String
Dim rangeArgl As String
Dim rangeArgl2 As String
Dim cnt As Integer
rangeArgl = Arg1.Address
rangeArgl2 = Arg12.Address

Dim LookInHere As String
Dim Counter As Integer
Dim SplitCatcher As Variant
Dim strA As String
Dim strBall1 As String
Dim strBall2 As String
Dim strBall3 As String
Dim strBall4 As String
Dim strBall5 As String
Dim strCurrentCellAddress As String
Dim intBallFound As Integer

Dim keepCnt As Integer
Dim intVal As Integer


'numbers selected from range
For Each iCell In Range(rangeArgl).Cells
varVal = varVal & iCell.Value & " "
Next iCell

LookInHere = varVal
SplitCatcher = Split(LookInHere, " ")
For Counter = 0 To UBound(SplitCatcher)
strA = SplitCatcher(Counter)
Select Case Counter
Case 0
strBall1 = strA & " "
Case 1
strBall2 = strA & " "
Case 2
strBall3 = strA & " "
Case 3
strBall4 = strA & " "
Case 4
strBall5 = strA & " "
End Select
Next


cnt = 0
intBallFound = 0
'check through data history
For Each iCell In Range(rangeArgl2).Cells
cnt = cnt + 1

If cnt = 5 Then

varVal2 = varVal2 & iCell.Value
keepCnt = keepCnt + 1
'check string for matches
If InStr(1, varVal2, strBall1) > 0 Then
intBallFound = intBallFound + 1
End If
If InStr(1, varVal2, strBall2) > 0 Then
intBallFound = intBallFound + 1
End If
If InStr(1, varVal2, strBall3) > 0 Then
intBallFound = intBallFound + 1
End If
If InStr(1, varVal2, strBall4) > 0 Then
intBallFound = intBallFound + 1
End If
If InStr(1, varVal2, strBall5) > 0 Then
intBallFound = intBallFound + 1
End If
varVal2 = ""
cnt = 0
If intBallFound > 0 Then
strCurrentCellAddress = "G" & keepCnt
Sheet17.Range(strCurrentCellAddress).Value = intBallFound & ""
End If
'ActiveWorkbook.Sheets("Sheet17").Cells(7, keepCnt).Value = intBallFound
intBallFound = 0
Else
varVal2 = varVal2 & iCell.Value & " "


End If


Next iCell



End Function

 

[vladi305]

The InStr function isn't finding the desired value

Let's say I have 21 or 17 on the string

If being looked for 1 it will return true because it found 1 on the 21 or the 17
I added an extra space and it return false on the 17 but on the 21 returns true

 

[bebo021999]

Is easier for helpers if you can:
1- share data via XL2BB (XL2BB - Excel Range to BBCode)
2- manual input some expected results
instead of image and code.

 

[vladi305]

Is easier for helpers if you can:
1- share data via XL2BB (XL2BB - Excel Range to BBCode)
2- manual input some expected results
instead of image and code

 

[vladi305]

I've already try using that Xl2BB and it didn't work for me

 

[bebo021999]

I've already try using that Xl2BB and it didn't work for me

How about sharing file via gg drive?

 

[vladi305]

How about sharing file via gg drive?

I can share it yes I use onedrive but will try using gg drive

 

[vladi305]

Here's the link for the spreadsheet

Fantasy5vba_Apr9 - Copy.xlsm

drive.google.com

 

[vladi305]

The set of numbers on the left side are drawings and the one the right side are numbers chosen
I already have a function working that check if any of the set have won but
Again what need to be accomplished now is how many sets match within that string

If the numbers chosen are 4 - 7 - 11 - 15 - 33 and the drawing is 3 - 7 - 11 - 21 36 then 2 numbers matched

 

[bebo021999]

Try to follow my 2nd request in #3: "manual input some expected results", instead of the unworking UDF